Skip to Content

Resultset value for ambiguous column in Open SQL Join

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    May 24, 2017 at 02:04 PM

    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.'

    Add comment
    10|10000 characters needed 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!