Outer Join

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).

The Query (a part as I currently got is): SELECT tblHeader.HCode, tblHeader.HName, tblHeader.HMisc, tblProg.ProgName
FROM tblHeader, tblProg
WHERE tblProg.ProgCode = NVL(tblHeader.HProgCOne, tblHeader.HProgCTwo) AND tblProg.ProgFilter IN (3,9);

The above query works partly. It works fine if either tblHeader.HProgCOne or tblHeader.HProgCTwo as got value and also satisfies that tblProg.ProgFilter is either 3 or 9.

It doesn't/can't return other rows/record from tblHeader table if tblHeader.HProgCOne or tblHeader.HProgCTwo are Null or doesn't satisfy tblProg.ProgFilter condition.

The Requirement:
I need to alter the above query so that all the rows of tblHeader is returned with the ProgName from tblProg which satisfies the above condition.

I tried an outer join on the Where Clause with NVL condition and it backfired. I don't want a correlated subquery.

Please do point me towards the right direction. Thanx in Advance.

Regards
Bala Received on Mon Nov 20 2006 - 20:13:38 CST