RE: SQL Left Outer Join with WHERE phrase

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

Roger, this is exactly what I would expect.  Remember that using a left
outer join can result in NULL value.  In this case, you just told it to
throw out all rows that do not have ' ' in T02.DELETE.  That of course
includes null too, so yes, you hace effectively turned the whole thing into
full inner join. 

If your intent here is to ensure that you don't join any deleted records in
the Left outer join, then just put the delete test into the ON clause of the
join.  Then the deleted rows won't even be consider for the join, and the
T02 fields can return null when necessary.


  FROM OCNDTLPF T01 LEFT OUTER JOIN NOTBLDPF T02 ON                 
       (T01.CONTRL = T02.CONTRL AND T01.ODCTR = T02.ODCTR AND T02.DELETE='
')          
       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%'                                    

Roger, as you can see, our venerable AS400 does just fine and is NOT in fact
falling behind in this regard.  <rude stuff about PC snobery deleted>.....
:(




Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Mackie, Roger L. (Precision Press) [mailto:RLMackie@xxxxxxxxxxxxx]
Sent: Wednesday, October 22, 2003 8:13 AM
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.