AW: SQL error after upgrade to V7

Hi Jim,

AFAIK using different join methods within the FROM clause, i.e. listing
tables and joining them in where conditions and (left/right
outer/inner/exception/full/cross joins) with join condition in the from
clause (ON ...) are no longer allowed.
You need to change your SQL statement as follows:

SELECT
DMNAME,DMSOURCE,SRDESC,DMREV,DMDESC,DMSTATUS,STSDESC,DMORDER,DMOWNER,WGDESC,
DMASGN,DMCHECK,
(SELECT COUNT(*) FROM COWOC WHERE OCNAME = DMNAME) NOTE
FROM COWDOC join COWSR on DMSOURCE = SRSOURCE (????)
Join COWSTS on DMSTATUS = STSCODE (????)
Join COWWG on DMOWNER = WGUSER (????)
Join HDMCATS A 0n DMNAME = A.DOCID (????)
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 DMSTATUS = 'N' AND A.CAT = 'CUSTPO';


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)

"If you think education is expensive, try ignorance." (Derek Bok)

"What is worse than training your staff and losing them? Not training them
and keeping them!"



-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jim Oberholtzer
Gesendet: Monday, May 14, 2012 17:43
An: Midrange Systems Technical Discussion
Betreff: SQL error after upgrade to V7


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.