Re: AW: SQL error after upgrade to V7

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

With appropriate (and probably confusing) use of parentheses, his 
original can probably be done - no time to work it out, of course!

Vern

On 5/14/2012 10:58 AM, BirgittaHauser wrote:
> 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.