Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Outer Join

Re: Outer Join

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Nov 2006 19:27:59 -0800
Message-ID: <1164079679.394255.144390@j44g2000cwa.googlegroups.com>


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

I am not sure that I follow the logic. It appears that you want to return all rows in TBLHEADER and those rows in TBLPROG that have a matching PROGCODE.

If you set up a left outer join (I changed the column names for simplification):
TBLHEADER.COLPROG = TBLPROG.COLPROG(+) The second condition in your WHERE clause essentially eliminates the possibility of NULL values for the PROGFILTER column, which would essentially change the above outer join to: TBLHEADER.COLPROG = TBLPROG.COLPROG. If NULLs do not occur naturally in the PROGFILTER column, the solution is very simple to work around NULLs that might occur in an outer join situation. If NULLs do occur naturally in the PROGFILTER column, then you might need to do a bit more work. This might get you close: NVL2(TBLPROG.PROGCODE,TBLPROG.PROGFILTER,3) Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Mon Nov 20 2006 - 21:27:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US