Re: SQL Question: How the outer join works

[Tony DeLia <tdelia@EROLS.COM>]

Joe,
   The Outer Join would be against the ACTN_REASON_TBL... be careful
though... it's an effective dated table so there could be multiple rows
returned for an Action/Reason combination... which would duplicate the
JOB/PERSONAL_DATA rows... and you cannot perform a sub-select against
the object of an outer join...

SELECT JOB.ACTION,
       JOB.DEPTID,
       JOB.EMPLID,
       ACT.DESCR,
       PER.NAME
  FROM PS_JOB                JOB,
       PS_ACTN_REASON_TBL    ACT,
       PS_PERSONAL_DATA      PER
 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          = PER.EMPLID
   AND JOB.ACTION          = ACT.ACTION        (+)
   AND JOB.ACTION_REASON   = ACT.ACTION_REASON (+)
 ORDER BY JOB.REPORTING_LOCATION,
          JOB.EMPL_TYPE,
          PER.NAME

You could create a view returning the current action/reason entries
(instead of the ACTN_REASON_TBL itself)... there's other alternatives as
well... this may not be an issue now but when a user enters an
effective-dated description change you'll see some duplication...

      Tony DeLia


Peters, Brian wrote:
>
> 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

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com
http://www.sqrtools.com