Re: Outer Joins

[Cherno Jagne <ITSCJ@QE2-HSC.NS.CA>]

You can first check whether the effdt (or any
column, for that matter) returned for your jbl
table is null and do the effdt logic only if it's
not.

ie., replace the lines you commented out with
the following:

 and (jbl.effdt is null
  or
 (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)))

Cherno.


>>> Kathy Mason <kmason@GIX-GLOBAL.COM> 06/20 3:44 PM >>>
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