Skip to Content
0

Resultset value for ambiguous column in Open SQL Join

May 24, 2017 at 12:56 PM

39

avatar image

Dear Experts,

I would like to know the behaviour of an Open SQL Statement where a column is ambiguously selected from several tables.

Example (assuming zentity1 and zentity2 both have a column col2 with different values for the join condition of col1):

        SELECT  *
         FROM zentity1
                         INNER JOIN zentity2

                         ON zentity1~col1 = zentity2~col1

         INTO CORRESPONDING FIELDS OF TABLE @result
.

Can you control in advance whether the value of zentity1~col2 or of zentity2~col2 will be copied to the result table?

When testing, it seemed like the column value of the table specified last (in this case zentity2) would be copied. When specifiying a projection other than * (e.g. SELECT zentity1~col2, zentity2~col2), the value of the column specified last would be copied.


Can you rely on this behaviour? Or is there a way to retrieve all columns of zentity1 except for the ambiguous ones, without specifying the whole column list (e.g. by excluding columns explicitly)?



Thanks for your suggestions,


Robin

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Raymond Giuseppi
May 24, 2017 at 02:04 PM
1

Doesn't the online documentation for CORRESPONDING FIELDS OF explicitly states that 'if CORRESPONDING FIELDS is specified. If a column name appears multiple times and no alternative column name was granted, the last column listed is assigned.'

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Unfortunately, I missed this information since I checked section CORRESPONDING FIELDS OF TABLE of the documentation, not the section about single work areas. As there is a reference to the addition linked by you, stating that the rules for single work areas also apply on a multi-row result set, your reply answers my question.

Thanks for your help!

0