|
We figured it out--put the WHERE phrase in the LEFT OUTER JOIN phrase like this: LEFT OUTER JOIN NOTBLDPF T02 ON (T01.CONTRL = T02.CONTRL AND T01.ODCTR = T02.ODCTR AND T02.DELETE = ' '). Hope this helps someone who does an archive search. Roger Mackie -----Original Message----- From: Mackie, Roger L. (Precision Press) [mailto:RLMackie@xxxxxxxxxxxxx] Sent: Wednesday, October 22, 2003 8:13 To: 'Midrange Systems Technical Discussion' Subject: SQL Left Outer Join with WHERE phrase Hi group, How can we get the AS/400 server to respect a LEFT OUTER JOIN from a client when there is a WHERE phrase on the outer file? Currently we have a query that fills a file that is queried by MS Excel. We are trying to get rid of the result file by having MS Excel query the files. RTVQMQRY gave us a place to start. Using RTVQMQRY on a *QRYDFN, we retrieved the following SQL statement: SELECT ALL T03.CSR, T01.ODBUD, T01.ODDDAT, T01.ODSDAT, T03.ORDTYP, T01.CONTRL, T01.ODCTR, T01.ACCTNO, T01.ITEMNO, T01.USHIP, T01.MLINE, T02.REASON, T01.TRACK, UPRICE*UORDER AS UP, POPRIC*(UORDER) AS PP FROM DTABUD/OCNDTLPF T01, DTABUD/NOTBLDPF T02, DTABUD/OCNHDRPF T03 WHERE T02.CONTRL = T01.CONTRL AND T03.CONTRL = T01.CONTRL AND T02.ACCTNO = T01.ACCTNO AND T02.ODCTR = T01.ODCTR AND( ODBUD IN (1, 6) AND TRACK IN ('5', '6') AND "DELETE" = ' ' AND ODDLT = ' ' AND T01.ACCTNO < 99999 AND CTRCTT <> 'X' AND ITEMNO NOT LIKE 'PF%' AND ITEMNO NOT LIKE 'AD%' AND OHDLT <> 'X') ORDER BY T03.CSR ASC, T01.ODDDAT ASC The query result file on the AS/400 (v5r1, see below) has all the records in T01, whether there is a record in T02 or not (LEFT OUTER JOIN on T02--what we want). If we copy and paste this same query into STRSQL or MS Excel, the result set has only inner join rows. We got the expected results in STRSQL with this LEFT OUTER JOIN that omits any reference to T02 in the WHERE clause: SELECT ALL T03.CSR, T01.ODBUD, T01.ODDDAT, T01.ODSDAT, T03.ORDTYP, T01.CONTRL, T01.ODCTR, T01.ACCTNO, T01.ITEMNO, T01.USHIP, T01.MLINE, T02.REASON, T01.TRACK, UPRICE*UORDER AS UP, POPRIC*(UORDER) AS PP FROM OCNDTLPF T01 LEFT OUTER JOIN NOTBLDPF T02 ON (T01.CONTRL = T02.CONTRL AND T01.ODCTR = T02.ODCTR) INNER JOIN OCNHDRPF T03 ON (T01.CONTRL = T03.CONTRL) WHERE (T01.ODBUD IN (1, 6) AND T01.TRACK IN ('5','6')) AND T01.ODDLT = ' ' AND T03.OHDLT = ' ' AND T01.ACCTNO < 99999 AND T01.CTRCTT <> 'X' AND T01.ITEMNO NOT LIKE 'PF%' AND T01.ITEMNO NOT LIKE 'AD%' ORDER BY T03.CSR ASC, T01.ODDDAT ASC However, when we added the phrase "T02.DELETE = ' '" to the WHERE clause, we got the results of an inner join instead of outer join. It looks to us like legacy behavior from before the days when we could specify inner and outer joins. Is there a way around this problem? Or will our platform sink lower in the eyes of our PC programmers because OS/400 was so far ahead of its time? Any help gratefully received, Roger Mackie _______________________________________________ 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.