Re: Left Outer Join in SQR

[Manoj Gurjar <mgurjar@HOTMAIL.COM>]

I don't know abt DB2,

But I would do like this in Informix, Oracle :

Informix :
begin-select

Select
T.ssn,
T.event_dt
Z.Status

from tableT T, OUTER tableZ Z
where
T.ssn  = Z.ssn
[get max effdted row]

end-select

Oracle :
begin-select

Select
T.ssn,
T.event_dt
Z.Status

from tableZ Z, tableT T
where
Z.ssn  = T.ssn (+)
[get max effdted row]

end-select

I have not tested for this perticular kind example.

HTH,
Manoj

>From: Norman Dolph <NEDOLPH@AOL.COM>
>Reply-To: SQR-USERS@list.iex.net
>To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
>Subject: Left Outer Join in SQR
>Date: Tue, 9 Nov 1999 19:32:07 EST
>
>                                        The Problem:
>
>Assume Table "T" holds data for BOTH Employees AND Applicants - 4 rows.
>
>      T.SSN        T.EventDate
>      111-11-1111  1999-08-01
>      111-11-1111  1999-11-01
>      222-22-2222  1999-08-01
>      333-33-3333  1999-08-01
>      444-44-4444  1999-08-01
>
>Table "Z" holds ONLY Employees, is keyed on the SSN, and EFFECTIVE DATED.
>(No rows for Applicants, 1 or More Effective dated rows Per Employee.
>
>      Z.SSN        Z.EffDate   Z.Status (never null or empty)
>      111-11-1111  1999-10-01  'OK'
>      111-11-1111  1999-01-01  'NG'
>      222-22-2222  1999-11-01  'A1'
>      333-33-3333  1999-06-01  'XX'
>      333-33-3333  1999-01-01  'OK'
>
>The Query should return the status of each person when the event happened.
>If an Employee,  show the status on the EventDate
>If an Applicant, show the row with the status field empty
>(... thus an empty status field will indicate who was an applicant at the
>time. even though he may be an employee NOW.)
>
>This is the desired answer:
>      T.SSN          T.EventDate Z.Status
>      111-11-1111  1999-08-01  'NG'
>      111-11-1111  1999-11-01  'OK'
>      222-22-2222  1999-08-01        (null, empty, blank)
>      333-33-3333  1999-08-01  'XX'
>      444-44-4444  1999-08-01        (null, empty, blank)
>
>In SQR I have tried everything, including NOT EXISTS in a subquery on Table
>Z.
>I either get a Cartesian join of everybody and everything, OR;
>I can ONLY RETRIEVE ROWS FROM THE EMPLOYEES, i.e. the rows with a status.
>                --- Applicants at event time are always missing! ---
>
>How can I make a Left Outer Join In SQR that will return the answer above.
>Or any other trick of mastery you have used to solve this kind of thing.
>
>Warmest thanks for any help,
>
>Norman Dolph
>(SQR for PeopleSoft 7.01 on DB2)
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com