Re: Outer Joins in SQR

[Tony DeLia <tdelia@EROLS.COM>]

Sandra/Albert,
   You absolutely CAN outer-join multiple tables AND use them in GROUP
BY functions... Some foresight may need to be applied but definitely
don't avoid them... There are some rules in outer-joining you must
adhere to... An example would be NO sub-selects against the tables that
is outer-joined... See OUTER JOIN rules for your particular database...

Also see 'Re: Dynamic SQR Select Clause - Tony DeLia' 10/27/1998 for
sample of Outer Join in SQR Program... note the handling of outer-joined
column in select list...

begin-select
....
decode(k.fieldname,a.fieldname,'Y',' ')           &k.key
....

  ! If fieldnames a/k are equal the outer-join returned a row..
  ! If not equal the outer-join returned a null row...
  ! &k.key is set to 'Y' if matching... else blank...

 from psrecfield        a,
      psdbfield         b,
      pskeydefn         k
where a.recname       = $rec
  and a.fieldname     = b.fieldname
  and k.recname   (+) = a.recname
  and k.fieldname (+) = a.fieldname
  and k.indexid   (+) = '_'
....


How about an example of MULTIPLE tables using outer-join...
Tables b, c, d, e, f, g are ALL outer-joined...

/* ***************************************************************** */
/* *                                                               * */
/* *    MODULE: OJPLAN.SQL                                         * */
/* *    AUTHOR: TONY DELIA.                                        * */
/* *      DATE: 06/12/96.                                          * */
/* *      DESC: RETIREMENT/PROFIT SHARING PLAN LISTING.            * */
/* *     USAGE: start ojplan <code1> <code2> <code3> <code4>       * */
/* *                                                               * */
/* *   EXAMPLE: @ojtemp N B R P   - Returns ALL types.             * */
/* *            @ojtemp R x x x   - Returns Retirement Only.       * */
/* *            @ojtemp R P x x   - Returns Ret OR PShr ONLY.      * */
/* *                                                               * */
/* ***************************************************************** */

SET ECHO     OFF
SET WRAP     OFF
SET PAUSE    OFF
SET FEEDBACK OFF
SET SPACE 2
SET LINESIZE 80
SET PAGESIZE 60

DEFINE a1 = '&1'
DEFINE a2 = '&2'
DEFINE a3 = '&3'
DEFINE a4 = '&4'

TTITLE 'JMH Retirement/Profit Sharing - OJPLAN.SQL'     SKIP 2 -
       'Types N=NONE, B=BOTH, R=RETR Only, P=PSHR Only' SKIP 1

COL hid   HEADING 'ID'                                  FORMAT A5
COL hname HEADING 'Name'                                FORMAT A15
COL hcode HEADING 'Plan|Type'                           FORMAT A10
COL hindr HEADING 'RET|Ind'                             FORMAT A3
COL hindp HEADING 'PSh|Ind'                             FORMAT A3
COL hcomp HEADING 'Co'                                  FORMAT A3
COL hstat HEADING 'Status'                              FORMAT A6
COL hdep  HEADING 'Dep|Ben'                             FORMAT A3
COL halt  HEADING 'Alt|Payee'                           FORMAT A5

select j.emplid                                         hid,
       p.name                                           hname,
       decode(b.emplid,null,
              decode(c.emplid,null,'NONE','PSHR'),
              decode(c.emplid,null,'RETR','BOTH'))      hcode,
       b.jmh_rp_partic_ind                              hindr,
       c.jmh_psh_partic_ind                             hindp,
       j.company                                        hcomp,
       j.empl_status                                    hstat,
       nvl(f.dependent_benef,d.dependent_benef)         hdep,
       decode(f.dependent_benef,null,
              e.jmh_depen_type_alp,
              g.jmh_depen_type_alp)                     halt
  from ps_job              j,
       ps_personal_data    p,
       ps_jmh_rp_em        b,
       ps_jmh_psh_em       c,
       ps_jmh_benefcry     d,
       ps_dependent_benef  e,
       ps_jmh_alt_payee    f,
       ps_dependent_benef  g
 where p.emplid               = j.emplid
   and (exists (select 'X'
                  from ps_jmh_rp_em    b2
                 where b2.emplid     = j.emplid
                   and b2.empl_rcd#  = j.empl_rcd#)
    or  exists (select 'X'
                  from ps_jmh_psh_em   c2
                 where c2.emplid     = j.emplid
                   and c2.empl_rcd#  = j.empl_rcd#))
   and j.effdt                =
       (select max(j2.effdt)
          from ps_job           j2
         where j2.emplid      = j.emplid
           and j2.empl_rcd#   = j.empl_rcd#
           and j2.effdt      <= sysdate)
   and j.effseq               =
       (select max(j3.effseq)
          from ps_job           j3
         where j3.emplid      = j.emplid
           and j3.empl_rcd#   = j.empl_rcd#
           and j3.effdt       = j.effdt)
   and b.emplid          (+)  = j.emplid
   and b.empl_rcd#       (+)  = j.empl_rcd#
   and c.emplid          (+)  = j.emplid
   and c.empl_rcd#       (+)  = j.empl_rcd#
   and d.emplid          (+)  = j.emplid
   and d.empl_rcd#       (+)  = j.empl_rcd#
   and e.emplid          (+)  = d.emplid
   and e.dependent_benef (+)  = d.dependent_benef
   and f.emplid          (+)  = j.emplid
   and f.empl_rcd#       (+)  = j.empl_rcd#
   and g.emplid          (+)  = f.emplid
   and g.dependent_benef (+)  = f.dependent_benef
   and decode(b.emplid,null,
       decode(c.emplid,null,'N','P'),
       decode(c.emplid,null,'R','B')) in ('&1','&2','&3','&4')
 order by j.emplid asc
/
CLEAR BREAKS
TTITLE OFF
CLEAR COLUMNS
SET FEEDBACK ON
SET WRAP ON
SET ECHO ON



                                                -Tony DeLia


Albert Howard wrote:
>
> Sandra,
>
> Mainly as I understand it, only one table can be outer joined in Oracle.
>
> Best tip is to avoid it by using other paragraphs to bring in associated
> data whereever possible.  But if you are trying to sort or group by data in
> the outer-joined table, obviously, thats not an option.
>
> In that case, follow the other examples provided in this thread, or you
> could create a temporary work table to store data, then retrieve it in the
> sorted or grouped fashion.

--
Tony DeLia
AnswerThink Consulting Group
PeopleSoft Solutions Practice - Delphi Partners
tdelia@erols.com