Re: Outer Joins

[Tim Brown <tbrown@FAIRPOINT.COM>]

Other possibilities are to use an in-line view of the records having the max
effective date then use an outer join with that view or use nvl to insure
you return some sort of date for the subquery.

I think union is the best

Tim


-----Original Message-----
From: Dave Pena (MBH) [mailto:dpena@MBHCONSULTING.COM]
Sent: Tuesday, June 20, 2000 3:10 PM
To: SQR-USERS@list.iex.net
Subject: Re: Outer Joins


Yeah, outer joins can be tricky, esp. when correlated subqueries are
involved.  The way I've gotten around these situations is to use a
union.  The first SELECT will retrieve people in both tables, the second
select will get those who aren't in the second table (using NOT
EXISTS).  I'm sure there are other solutions, but this is mine.

HTH.
Dave.

At 6/20/00 02:44 PM , Kathy Mason wrote:
>I have a question about outer joins.  We have an Oracle database with a
>UNIX OS.
>
>It appears that one cannot use effective date criteria on the table that is
>outer joined.  Is that correct?  I can only make the select below work,
when I
>comment out the effective dates on the outer joined table.  If I am doing
>something wrong, I would appreciate it if someone could tell me.
>
>Begin-Procedure Select-Data
>Begin-SELECT
>A.SCHED_ID
>A.EMPLID
>A.BENEFIT_PROGRAM
>B.NAME
>C.EFFDT
>C.DESCR
>D.SERVICE_DT
>jbl.UNION_CD
>
>FROM  PS_BAS_PARTIC    A,
>       PS_PERSONAL_DATA B,
>       PS_BEN_DEFN_PGM  C,
>       PS_EMPLOYMENT    D,
>       ps_job_labor jbl
>WHERE A.SCHED_ID LIKE $Sched_Id
>AND   A.PROCESS_IND = 'N'
>AND   A.EVENT_CLASS <> 'TER'
>AND   A.BENEFIT_RCD#    =
>       (SELECT MAX(BENEFIT_RCD#)
>        FROM   PS_BAS_PARTIC
>        WHERE  SCHED_ID     = A.SCHED_ID
>        AND    EMPLID       = A.EMPLID
>        AND    BENEFIT_RCD# = A.BENEFIT_RCD#
>        AND    EVENT_ID     = A.EVENT_ID)
>AND   B.EMPLID          = A.EMPLID
>AND   C.BENEFIT_PROGRAM = A.BENEFIT_PROGRAM
>AND   C.EFFDT = (SELECT MAX(EFFDT)
>                  FROM   PS_BEN_DEFN_PGM
>                  WHERE  BENEFIT_PROGRAM = C.BENEFIT_PROGRAM
>                  AND    EFFDT          <= A.EVENT_DT)
>AND   D.EMPLID          = A.EMPLID
>AND   D.BENEFIT_RCD#    = A.BENEFIT_RCD#
>AND   D.EMPL_RCD#       = A.EMPL_RCD#
>  and d.emplid = jbl.emplid(+)
>   and d.empl_rcd# = jbl.empl_rcd#(+)
>  ! and jbl.effdt = ( SELECT MAX(jbl1.EFFDT) FROM PS_JOB_labor jbl1
>   !                WHERE jbl1.EMPLID = jbl.EMPLID
>   !                  AND jbl1.EMPL_RCD# = jbl.EMPL_RCD#
>   !                  AND jbl1.EFFDT <= A.EVENT_DT)
>   !AND jbl.EFFSEQ = (SELECT MAX(jbl2.EFFSEQ) FROM PS_JOB_labor jbl2
>   !                WHERE jbl2.EMPLID = jbl.EMPLID
>   !                  AND jbl2.EMPL_RCD# = jbl.EMPL_RCD#
>   !                  AND jbl2.EFFDT = jbl.EFFDT)
>End-SELECT
>End-Procedure
>
>******************
>
>Thanks,
>Kathy Mason