topic Re: Resultset value for ambiguous column in Open SQL Join in Application Development and Automation Discussions

Resultset value for ambiguous column in Open SQL Join

Former Member — Wed, 24 May 2017 12:56:18 GMT

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

Re: Resultset value for ambiguous column in Open SQL Join

RaymondGiuseppi — Wed, 24 May 2017 14:04:05 GMT

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

Re: Resultset value for ambiguous column in Open SQL Join

Former Member — Wed, 24 May 2017 15:08:35 GMT

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!