Re: SQL Difference in V7 vs V6 not in memo to users

On 30-Sep-2011 13:04 , Jeff Young wrote:

I just got bitten by this error today:

Message ID . . . . . . . . . : SQL0338
Message file . . . . . . . . : QSQLMSG
Library . . . . . . . . . : QSYS
Message text . . . . . . . . : JOIN predicate not valid.

<<SNIP>>

It seems that in V7, if you have an implicit join, followed by an
explicit join, any fields on the explicit join must be from either
the last implicit joined file or the explicit joined file. In other
words, select a,b,c,d from x,y left outer join z on a = r and b = s
where a and b are fields in file x and r and s are fields in file z
works just fine on V6,but not on V7.

The precedence of explicitly joined files supersedes that of the implicitly joined files. Thus the above example should be treated by the SQL as the following statement with the precedence parenthetically expressed, whereby the lack of fields "a" and "b" in the file named "y" should make the origin for the error obvious:

select a,b,c,d from x, ( y left outer join z on a=r and b=s )

I wonder what else they changed but did not put into the memo to
users.

The issue was documented in the MTU for v6r1. So if the error was not occurring for V6R1, then I believe the lack of an error on that release would be a defect issue rather than a documentation issue for lack of that MTU snippet for the v7r1 MTU; i.e. where the two releases should AFaIK exhibit the same behavior, with both diagnosing the -338 condition.

http://archive.midrange.com/midrange-l/200909/msg00749.html

Ref: "Resolving a SQL0338 failure" in the V6R1 Memo To Users
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic//rzaq9/rzaq9.pdf

Regards, Chuck