|
SELECT ALL SUBSTR(T03.SCDSSS,1,6) AS GLACCT, T01.INVNCV, T01.CUNOCV, T04.CUSNM, T01.IVDTCV, T02.LISQVS, T03.SCDSSS, (T03.SCAMSS) FROM AMFLIB/CUSINVL0 T01 inner join AMFLIB/CIVSPCL0 T02 on ( T01.INVNCV = T02.INVNVS AND T01.INSQCV = T02.INSQVS ) inner join AMFLIB/CUSMAS T04 on ( T01.CONOCV = T04.COMNO AND T01.CUNOCV = T04.CUSNO ) inner join AMFLIB/COSSPCL0 T03 -- this is the different line on ( T02.ORDRVS = T03.ORDRSS AND T02.SHSQVS = T03.SHSQSS AND T02.SCSQVS = T03.SCSQSS ), Where T01.CONOCV = 01 AND T01.ICDTCV BETWEEN 1040701 AND 1040731 AND T03.SCCDSS = '2' ORDER BY GLACCT ASC, T01.INVNCV ASC
The difference is on line 11 of the statement you had.
HTH Vern
Vern, I checked out the Query Management Programming manual, scanned on "join" and found nothing of value.
I modified an unfruitful archive search and found some other relevant information. So this is what I've modified my original SQL to:
SELECT ALL SUBSTR(T03.SCDSSS,1,6) AS GLACCT, T01.INVNCV, T01.CUNOCV, T04.CUSNM, T01.IVDTCV, T02.LISQVS, T03.SCDSSS, (T03.SCAMSS) FROM AMFLIB/CUSINVL0 T01 inner join AMFLIB/CIVSPCL0 T02 on ( T01.INVNCV = T02.INVNVS AND T01.INSQCV = T02.INSQVS ) inner join AMFLIB/CUSMAS T04 on ( T01.CONOCV = T04.COMNO AND T01.CUNOCV = T04.CUSNO ), AMFLIB/COSSPCL0 T03 Where ( T02.ORDRVS = T03.ORDRSS AND T02.SHSQVS = T03.SHSQSS AND T02.SCSQVS = T03.SCSQSS ) AND T01.CONOCV = 01 AND T01.ICDTCV BETWEEN 1040701 AND 1040731 AND T03.SCCDSS = '2' ORDER BY GLACCT ASC, T01.INVNCV ASC
The change is that I moved the pseudo-join Where's to an inner join and removed the "selection" from the Where clause for the T01 <-- T02 and T01 <-- T04 joins. This works as well as the original.
Where I failed was trying to join T03 to T02; I'm getting errors like "Duplicate table designator T02 not valid" and "Column qualifier or table T03 undefined". Here's what I've tried:
SELECT ALL SUBSTR(T03.SCDSSS,1,6) AS GLACCT, T01.INVNCV, T01.CUNOCV, T04.CUSNM, T01.IVDTCV, T02.LISQVS, T03.SCDSSS, (T03.SCAMSS) FROM AMFLIB/CUSINVL0 T01 inner join AMFLIB/CIVSPCL0 T02 on ( T01.INVNCV = T02.INVNVS AND T01.INSQCV = T02.INSQVS ) inner join AMFLIB/CUSMAS T04 on ( T01.CONOCV = T04.COMNO AND T01.CUNOCV = T04.CUSNO ), AMFLIB/CIVSPCL0 T02 inner join AMFLIB/COSSPCL0 T03 on ( T02.ORDRVS = T03.ORDRSS AND T02.SHSQVS = T03.SHSQSS AND T02.SCSQVS = T03.SCSQSS ), Where T01.CONOCV = 01 AND T01.ICDTCV BETWEEN 1040701 AND 1040731 AND T03.SCCDSS = '2' ORDER BY GLACCT ASC, T01.INVNCV ASC
What am I missing?
tia, db
-- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
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.