Re: PL/SQL: Out Joins omit some records - how to fix?

From: Jurij Modic <jmodic_at_src.si>
Date: Mon, 18 Jan 1999 20:58:02 GMT
Message-ID: <36a3a02e.895266@news.arnes.si>

On Sun, 17 Jan 1999 14:26:12 -0800, "John Haskins" <76054.334_at_compuserve.com> wrote:

>Can anyone tell me how to make a query with multiple outer joins return a
>row even if one of the outer joins does not find a matching detail record?
>
> ...[SNIP].....
>Here is a sample of the PL/SQL routine, which is going against an Oracle
>7.2.3 database:
>
>===============================================
>create or replace procedure POPULATE_OUTPUT_TABLE is
> V_SOURCEROW OUTPUT_TABLE%rowtype;
> cursor TEMP_CURSOR is
> select
> ...[SNIP].....
> where
> M.MASTER_ID = D1.MASTER_ID (+)
> and
> D1.SEQUENCE_NUMBER = 1
> and
> M.MASTER_ID = D2.MASTER_ID (+)
> and
> D2.SEQUENCE_NUMBER = 2
> and
> M.MASTER_ID = D3.MASTER_ID (+)
> and
> D3.SEQUENCE_NUMBER = 3
> and
> M.MASTER_ID = D4.MASTER_ID (+)
> and
> D4.SEQUENCE_NUMBER = 4
> and
> M.MASTER_ID = D5.MASTER_ID (+)
> and
> D5.SEQUENCE_NUMBER = 5
> ...[SNIP].....
Whenever there is less than 5 detail records for a master record your cursor *can't* return any record because you don't use outer join operator in sequence_number evaluations in the WHERE clause. Whenever there is a null-generated detail record being created because of the outer join the following condition will avaluate to false because SEQUENCE_NUMBER is NULL for this record:

.... and Dx.SEQUENCE_NUMBER = x .... (x is 1..5)

Replace all of this conditions with the following:

.... and Dx.SEQUENCE_NUMBER(+) = x .... (x is 1..5)

So your WHERE clause should look like:

    where

       M.MASTER_ID = D1.MASTER_ID (+)
       and
       D1.SEQUENCE_NUMBER(+) = 1
       and
       M.MASTER_ID = D2.MASTER_ID (+)
       and
       D1.SEQUENCE_NUMBER(+) = 2
       and
       M.MASTER_ID = D3.MASTER_ID (+)
       and
       D1.SEQUENCE_NUMBER(+) = 3
       and
       M.MASTER_ID = D4.MASTER_ID (+)
       and
       D1.SEQUENCE_NUMBER(+) = 4
       and
       M.MASTER_ID = D5.MASTER_ID (+)
       and
       D5.SEQUENCE_NUMBER(+) = 5;

HTH, Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Jan 18 1999 - 14:58:02 CST