× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.