Re: SQL Question: How the outer join works

["Peters, Brian" <PetersB@HSN.NET>]

The table that is associated with the "+" contains the Null Row.

Brian
at HSN
PeopleSoft Implementation Team

-----Original Message-----
From: Rosie ODonnell [mailto:joe.patton@NA.MARCONICOMMS.COM]
Sent: Friday, July 30, 1999 1:21 PM
To: Multiple recipients of list SQR-USERS
Subject: SQL Question: How the outer join works


CSS HRizon 8.00h
Oracle 7.3.2
HP-UX 10.20
SQRW 3.0.13.2

I'm trying to figure out if a Reporting tool that I use(Reportsmith) handles
outer joins properly, and I also need a better understanding of which side
to
put the (+) on when using an outer join.  Here is my situation:

SELECT JOB.ACTION, JOB.DEPTID,  JOB.EMPLID, ACT.DESCR, PV_PERSONAL_DATA.NAME
FROM
PS_JOB JOB, PS_ACTN_REASON_TBL ACT, PV_PERSONAL_DATA PERS
WHERE
JOB.EFFDT BETWEEN '01-APR-1998' AND '31-MAR-1999'
AND JOB.EMPL_CLASS IN ( 'A', 'E')
AND JOB.ACTION IN ( 'TER', 'RET')
AND JOB.EMPLID = PV_PERSONAL_DATA.EMPLID
AND JOB.ACTION = ACT.ACTION
AND JOB.ACTION_REASON = ACT.ACTION_REASON(+)
ORDER BY
JOB.REPORTING_LOCATION, JOB.EMPL_TYPE, PERS.NAME

I am referencing the ps_actn_reason_tbl just to get a description.  Some of
the
job records have null ACTION_REASON fields, so they won't link with the
action/reason table.  Which side to I place the (+) on to make sure that the
job
records with the blank action_reason field still show up in the query
results?

Thanks,

Joe Patton
Marconi Communications