Re: SQL puzzle

Now this makes finally sense - I was about to try which kind of JOIN would be the result of that syntax, because no join direction was given. 

But as I always use the optional AS keyword, I haven't seen, that OUTER can be interpreted as an correlation name. 

Thanks for the clarification. 


> Am 04.04.2026 um 20:06 schrieb Sue Romano <[email protected]>:
> 
> This is an interesting observation.  The syntax is NOT doing what you intend. OUTER is being recognized as the table correlation name for TABLE1.  The join is then interpreted as an INNER join.
> 
> 
> 
> As the SQL syntax evolves, situations like this can occur.  I can't find anything specific that changed in IBM i 7.5 to trigger this new interpretation. While we could restrict OUTER as a table correlation name, that restriction would break existing applications that use it anywhere as a table correlation name.  I will have a discussion with others on the Db2 for i development team to decide whether this merits an incompatible restriction.
> 
> 
> 
> 
> 
>> Here's a conundrum. Consider the following SQL statement:
> 
>> 
> 
>> select t1_key, t1_value, t1_foreign, t2_key, t2_value  ? from table1 outer join table2  ? ? on t1_foreign = t2_key  ? order by t1_key;
> 
>> 
> 
>> If I read the SQL Reference (syntax diagram) correctly, this is wrong.
> 
>> It must be coded as LEFT OUTER JOIN, (or LEFT JOIN). And yet, my memory tells this has always been acceptable coding, with LEFT as the default.
> 
>> I fact, I tried it on V7.5 and V7.6 systems - and it works just fine
> 
>> 
> 
>> I also tried it on a V7.4 system - and it fails with:
> 
>> SQL0199 Keyword OUTER not expected. Valid tokens: FOR USE SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT OFFSET.
> 
>> 
> 
>> So, my conundrums are:
> 
>> 1). It is OK to code OUTER JOIN, (and LEFT is assumed)?
> 
>> 2). Is there a PTF required on the V7.4 system - or are there problems on the V7.5 and V7.6 systems?
> 
>> 3). Is the SQL Reference incorrect?
> 
> 
> 
> Sue Romano
> Db2 for IBM i development
> 
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
> 
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.