RE: SQL Left Outer Join with WHERE phrase -- MIDRANGE-L

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.