Re: Outer Join

In article <1164075218.175848.132780_at_b28g2000cwb.googlegroups.com>,  "bala" <balkiir_at_gmail.com> wrote:

> Hi Gurus
>
> It there anyway to combine an NVL clause with outer join ?
>
> Scenario:
>
> tblHeader
> HCode Number (PK)
> HProgCOne Varchar2(10) (FK -
> tblProg.ProgCode)
> HProgCTwo Varchar2(10) (FK -
> tblProg.ProgCode)
> HName Varchar2(30)
> HMisc Varchar(30)
>
> tblProg
> ProgCode Varchar2(10) (PK)
> ProgFilter Number(1) (Filter,
> we are concerned only with value 3, 9)
> ProgName Varchar2(40) (Prog Name)
>
> The columns 'HProgCOne' and 'HProgCTwo' in table tblHeader may or
> mayn't contain a value.
>
> The Current Situation:
> If the column tblHeader.HProgCOne as got value and tblProg.ProgFilter
> as got value 3 or 9 then ProgName should be returned.
>
> If the column tblHeader.HProgCOne is null then check whether
> tblHeader.HProgCTwo as got value and if tblHeader.HProgCTwo as got
> value and tblProg.ProgFilter for the corresponding as got value 3 or 9
> then ProgName should be returned.
>
> If both tblHeader.HProgCOne and tblHeader.HProgCTwo are null or doesn't
> satisfy tblProg.ProgFilter value of 3 or 9 then no ProgName is returned
> but all the rows from tblHeader must be displayed (irrespective of
> whether there is any value for tblHeader.HProgCone or
> tblHeader.HProgCTwo).

So, write the join clause exactly as you have said it.

select tblHeader.HCode, tblHeader.HName, tblHeader.HMisc,

       tblProg.ProgName
from tblHeader h left join tblProg p

    on p.ProgFilter IN (3,9)

       and NVL(h.HProgCOne, h.hProgCTwo) = p.ProgCode;

Remember that the ON clause simply determines which p rows join to h rows; it does not filter out from-clause results.

Karl Received on Tue Nov 21 2006 - 07:12:50 CST