Invalid column name; mismatched column in set (UNION ALL)

[Former Member]

I saw the Jody's proposed solution in this thread:    which seems great (which it probably is) and now I am trying to do the same, but it does not seem to work as I hoped.

PROCEDURE temp (IN a; OUT b)

...

DECLARE cond ...

...

CALL proc_demo(c);  -- c is returned (OUT) from proc_demo and contains columns 'id' & 'ref'

b = SELECT id AS field1 FROM :c WHERE ref = cond

      UNION ALL

      SELECT * FROM :a WHERE ref = cond;

Note: 'a' is based on an ABAP dictionary table and contains no column 'id', but column 'field1' among others.

The syntax error is: "Invalid column name; mismatched column in set (UNION ALL)"

Just for testing I also tried (which gives the same error):

b = SELECT field1 AS field1 FROM :a WHERE ref = cond

      UNION ALL

      SELECT * FROM 😛 WHERE ref = cond;

I would appreciate any help on this. Please note I am new to SQL so pls explain on a basic level

[lbreddemann]

Hmm... good to see that you're working your way through SQL Script.

But opening one question after another without even revisiting or closing the old ones isn't really nice.

Concerning your code above... well, you reference variable cond without colon (:) plus (and this leads to the error message) you perform a SELECT * on your table variable a.

As you wrote yourself this table variable has a different structure than "ID as FIELD1".

But UNION ALL (in every SQL dialect that I know) requires that the structures of all sub-selects are equal.

There you go. Now you know.

[Former Member]

Thanks for this helpful answer. Since I myself do not know any SQL dialects I was not aware that the sub-selects must be equal. I suspected this to be the case BUT then why do I get the same message with this coding:

b = SELECT field1 AS field1 FROM :a WHERE ref = :cond

      UNION ALL

      SELECT * FROM 😛 WHERE ref = :cond;

Note: a and b are both declared as the same type in the IN/OUT parameter declaration.

In this connection please note that your comment:

 you perform a SELECT * on your table variable a.

contradicts (at least it seems so to me) what Jody proposed:

a = SELECT 2 AS "MY_FIELD" FROM DUMMY UNION ALL SELECT "MY_FIELD" FROM :a;

in this thread:

Is Jody wrong or is his case and my case different?

FYI: I am leaving questions (threads) open as long as I dont have an answer that I understand. Maybe somebody will contribute something. I fail to understand why you find that "not nice".

[lbreddemann]

Nope, Jody is not wrong, but you stumble over SQL syntax here.

With your first sub-select you get a result set that looks like this:

FIELD1

With your second sub-select you get a result set that looks like this:

FIELD1 | FIELD2 | ... | FIELDx ---> ALL columns from table variable :b.

In his example, Jody select one column ("MY_FIELD") in the first sub-select and exactly one column in the second (again "MY_FIELD").

So, no, Jody did not propose at all that you do a SELECT *...

Just a hint: usually it helps to try out commands that you're not familiar with in the SQL console before packing them up in procedures. Finding the error in a single command is much easier that way.

[Former Member]

Thank you for this very helpful answer. Things are getting clearer now.

The concern that I am having is that the (IN/OUT) table variables in the procedure are based on a generated table in the ABAP dictionary.

Hence, if the generated table structure changes in the ABAP dictionary the change must be manually adjusted in either:

- the procedure (e.g. if I use an array to populate the OUT table variable) or

- in the global CDS table type (assuming I declare it as such to use in SQL).

Hence, albeit the table (which is the basis for the OUT table variable) appears under the HANA default schema it seems as if there is no solution whereby changes to the table variable structure (i.e. the ABAP dictionary structure) can be accomodate in the HANA SQL-logic dynamically.

[lbreddemann]

You are mixing development levels up here, which is why you get into these issues.

When you want to base your SAP HANA development on ABAP artifacts, you can either resort to ABAP Managed Database Procedures or you have to build encapsulation objects, e.g. views to have a stable interface structure.

Although it's now a "big shared database" it's still required to understand an application schema is not the database...

- Lars