Re: SQL error after upgrade to V7

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

Hi Jim

JOIN specifications were tightened up some at 6.1 - the solution is to 
parenthesize things, to tell the optimizer what you REALLY want it to do 
with all those LEFT OUTERs - I assume you want the behavior that each 
successive LEFT OUTER JOIN works against the result set of all preceding 
JOINs?

Here's the relevant bit from the Memo to Users from that release -

Resolving a SQL0338 failure

In V6R1, a query might fail with SQL0338 for queries with a JOIN. In 
earlier releases, this usage was allowed. The error needs to be issued 
because of the implied order of the join condition. To resolve the 
SQL0338 failure, add parentheses to the query to remove the ambiguity.

Select a.c1
From BASE1 a LEFT OUTER JOIN BASE3 c
LEFT OUTER JOIN BASE2 b on a.c1 = b.c1
on b.c1n = c.c1n

is equivalent to

Select a.c1
From BASE1 a LEFT OUTER JOIN (BASE3 c
LEFT OUTER JOIN BASE2 b on a.c1 = b.c1) on b.c1n = c.c1n

SQL0338 can also be issued when a table is specified followed by a comma 
and a joined table.

Select a.c1
From BASE1 a, BASE2 b INNER JOIN BASE3 c on a.c1 = c.c1

is equivalent to

Select a.c1
From BASE1 a, (BASE2 b INNER JOIN BASE3 c on a.c1 = c.c1)

To specify a.c1 in the ON clause for the joined table, include the first 
table within the joined table.

Select a.c1
From BASE1 a CROSS JOIN BASE2 b INNER JOIN BASE3 c on a.c1 = c.c1


I know - that doesn't solve it - but now you know why!

Vern

On 5/14/2012 10:43 AM, Jim Oberholtzer wrote:
> A Client just upgraded to V7 over the weekend.  (Test partition)
>
> We recreated the Host server SQL Package  QGPL/QZDAPKG but that did not
> clear the problem and this SQL is beyond my ability to debug.  Any clue
> as to what might be wrong?  It works well at V5R4.
>
> SELECT
> DMNAME,DMSOURCE,SRDESC,DMREV,DMDESC,DMSTATUS,STSDESC,DMORDER,DMOWNER,WGDESC,DMASGN,DMCHECK,
> (SELECT COUNT(*) FROM COWOC WHERE OCNAME = DMNAME) NOTE
> FROM COWDOC,COWSR,COWSTS,COWWG,HDMCATS A
> LEFT OUTER JOIN HDMCATS B ON (B.CAT = 'FILEICONS' AND B.IDX = 'FILEEXT' AND
> B.IDXVAL = A.FILEEXT AND B.STS IN('A','K'))
> LEFT OUTER JOIN COWOA ALERT1 ON (DMOA1 = ALERT1.OACODE)
> LEFT OUTER JOIN COWOA ALERT2 ON (DMOA2 = ALERT2.OACODE)
> LEFT OUTER JOIN COWOA ALERT3 ON (DMOA3 = ALERT3.OACODE)
> LEFT OUTER JOIN COWOA ALERT4 ON (DMOA4 = ALERT4.OACODE)
> LEFT OUTER JOIN COWOA ALERT5 ON (DMOA5 = ALERT5.OACODE)
> WHERE DMSOURCE = SRSOURCE
> AND DMSTATUS = STSCODE
> AND DMOWNER = WGUSER
> AND DMNAME = A.DOCID
> AND DMSTATUS = 'N'
> AND A.CAT = 'CUSTPO';
>
>
> Error is:
> SQL State: 42972
> Vendor Code: -338
> Message: [SQL0338] JOIN predicate or MERGE ON clause not valid.
>
> It is highlighting the "ON (DMOA1 = ALERT1.OACODE)" portion of the
> statement.