|
My apologies for such a short answer, but time is a valuable commodity today. IFNULL() is your friend.... HTH. L. Wayne James Senior Developer Agilysys, Inc. Hospitality Solutions Group 11545 Wills Road Alpharetta, GA 30004 Phone: 770-962-6425 x1252 Email: Wayne.James@xxxxxxxxxxxx "Dan Bale" <dbale@xxxxxxxxxxxxx> Sent by: midrange-l-bounces@xxxxxxxxxxxx 08/12/2004 09:09 AM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> To "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> cc Subject Left join question (was: SQL table joins: Join vs. Where) O.k., I've got this working now with: 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 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 Now, I would like to change the inner join to CUSMAS (T04) to a left join, on the off-chance we have a missing customer record. *But*, I would like to use a substituted value, like "* Customer # not found *' in the customer name field (T04.CUSNM) when there is no customer record. The frosting would be if I could specify "* Customer nnnnnnnn not found *', where 'nnnnnnnn' is the customer number. Oh, and I would ask again, why did RTVQMQRY surround the T03.SCAMSS field in the SELECT in parentheses? BTW, this exercise demonstrates the beauty of SQL. Even though I'm not proficient in it yet, I was able to take a working *QRYDFN, convert it to SQL and get the same results (the caveat being the "Match Type = 1" is the only join that will convert properly), tweak it do explicit joining, testing it along the way in interactive SQL and in Ops Nav. Last night I copied the SQL into an RPGLE program, the mainline for which is so simple. To do this in native I/O would have required building a workfile with CHAINs and SETLL/READEs, then reading it, probably two programs there, with a third to call the two; a PITA. It's blazingly fast, too: 200,000 records in the primary file, 100,000 in T02, 100,000 in T03, and 5,000 in T04; end up with about 1200 rows in less than two seconds! Obviously, YMMV and, yes, we've got the horses. <g> 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.