[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index] [Date Index] [Thread Index]
[SQR-USERS Info] [SQRUG Home Page]

Left Outer Join in SQR



                                       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)