Re: AMH AML APH APL GHH GLH

["Andrew Gaskin" <gemstone.solutions@xxxxxxxxxxxxxx>]

You know, as soon as I sent it I knew it was wrong :(

Just one thing: I think the "XRBEOR = 9" in the WHERE clause effectively
negates the outer join.  You may want to put (XRBEOR = 9 OR XRBEOR IS NULL)
or move the condition to be part of the outer join clause.



----- Original Message -----
From: Paul Holstein <holstein13@hotmail.com>
To: <BPCS-L@midrange.com>
Sent: Tuesday, February 15, 2000 12:09 AM
Subject: RE: AMH AML APH APL GHH GLH


> George,
>
> I went ahead and tried your second query but found that your join is
> incorrect.
>
> The field names should be XRHBNK, XRHTYP, etc. as follows:
>
> SELECT AMLBNK, AMLTYP, AMLREF, AMLSUB, AMLLIN, AMLIVN,
> AMLINV, AMLPDA, LHYEAR, LHPERD, HHTRNO, HHJNEN, LHJNLN, CRSG01,
> CRSG02, CRSG03, CRSG04, CRSG05, LHDRAM, LHCRAM, LHLSTS
> FROM AML LEFT OUTER JOIN GHHJ01 ON
> AML.AMLBNK = GHHJ01.XRHBNK AND AML.AMLTYP = GHHJ01.XRHTYP AND
> AML.AMLREF = GHHJ01.XRHREF AND AML.AMLSUB = GHHJ01.XRHSUB AND
> AML.AMLLIN = GHHJ01.XRLLIN
> LEFT OUTER JOIN GLH ON
> GHHJ01.XRLDGR = GLH.LHLDGR AND GHHJ01.XRBOOK = GLH.LHBOOK AND
> GHHJ01.XRYEAR = GLH.LHYEAR AND GHHJ01.XRPERD = GLH.LHPERD AND
> GHHJ01.XRJNEN = GLH.LHJNEN
> INNER JOIN GCR ON
> GLH.LHLDGR = GCR.CRLDGR AND GLH.LHIAN = GCR.CRIAN
> WHERE AMLPDA Between 19990900 AND 19990999 AND XRBEOR = 9
> ORDER BY AMLBNK, AMLREF, AMLLIN, HHJNEN, LHJNLN
>
> Again, I found the query to be extremely slow and would recommend an
access
> path and no sorting.
>
> --Paul Holstein
> iWork Software
>
> SELECT AML.AMLBNK, AML.AMLTYP, AML.AMLREF, AML.AMLSUB, AML.AMLLIN,
>    AML.AMLIVN, AML.AMLINV, AML.AMLPDA, GLH.LHYEAR, GLH.LHPERD,
>    GHHJ01.HHTRNO, GHHJ01.HHJNEN, GLH.LHJNLN, GCR.CRSG01,
>    GCR.CRSG02, GCR.CRSG03, GCR.CRSG04, GCR.CRSG05, GLH.LHDRAM,
>    GLH.LHCRAM, GLH.LHLSTS
> FROM  ((AML LEFT OUTER JOIN GHHJ01 ON
> (AML.AMLBNK = GHHJ01.XRBNK) AND
> (AML.AMLTYP = GHHJ01.XRTYP) AND
> (AML.AMLREF = GHHJ01.XRREF) AND
> (AML.AMLSUB = GHHJ01.XRSUB) AND
> (AML.AMLLIN = GHHJ01.XRLLIN)
> LEFT OUTER JOIN GLH ON
> (GHHJ01.XRLDGR = GLH.LHLDGR) AND
> (GHHJ01.XRBOOK = GLH.LHBOOK) AND
> (GHHJ01.XRYEAR = GLH.LHYEAR) AND
> (GHHJ01.XRPERD = GLH.LHPERD) AND
> (GHHJ01.XRJNEN = GLH.LHJNEN)
> INNER JOIN S102PHTM.BPCSFMMP.GCR GCR ON
> (GLH.LHLDGR = GCR.CRLDGR) AND
> (GLH.LHIAN = GCR.CRIAN))
> WHERE (AML.AMLPDA >= <Low Date>) AND
> (AML.AMLPDA <= <High Date>) AND
> (GXR.XRBEOR = 9)
> ORDER BY AML.AMLBNK, AML.AMLREF, AML.AMLLIN, GHHJ01.HHJNEN,
>      GLH.LHJNLN
>
>
> Best o' luck.
>
> George Sagen CPIM, CNE
> PrimeSource Technologies, Inc.
> A Metamor Worldwide Company
> gsagen@primesourcetech.com
> http://www.primesourcetech.com
> (801)360-6360 Mobile and VM
>
> +---
> | This is the BPCS Users Mailing List!
> | To submit a new message, send your mail to BPCS-L@midrange.com.
> | To subscribe to this list send email to BPCS-L-SUB@midrange.com.
> | To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner: dasmussen@aol.com
> +---
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
> +---
> | This is the BPCS Users Mailing List!
> | To submit a new message, send your mail to BPCS-L@midrange.com.
> | To subscribe to this list send email to BPCS-L-SUB@midrange.com.
> | To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner: dasmussen@aol.com
> +---
>

+---
| This is the BPCS Users Mailing List!
| To submit a new message, send your mail to BPCS-L@midrange.com.
| To subscribe to this list send email to BPCS-L-SUB@midrange.com.
| To unsubscribe from this list send email to BPCS-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: dasmussen@aol.com
+---