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

Hi John,

Pardon me if I misunderstand, but it looks like your sql is incorrect. IMHO it should have been

    select

        M.MASTER_ID,
        D1.CONSULT_MD    MD_CONS1,
        D2.CONSULT_MD    MD_CONS2,
        D3.CONSULT_MD    MD_CONS3,
        D4.CONSULT_MD    MD_CONS4,
        D5.CONSULT_MD    MD_CONS5
    from
        MASTER_TABLE    M,
        MASTER_TABLE    D1,
        MASTER_TABLE    D2,
        MASTER_TABLE    D3,
        MASTER_TABLE    D4,
        MASTER_TABLE    D5
    where
       ( M.MASTER_ID = D1.MASTER_ID (+)
        and
        D1.SEQUENCE_NUMBER = 1)

       OR
       (
        M.MASTER_ID = D2.MASTER_ID (+)
        and
        D2.SEQUENCE_NUMBER = 2
       )
        OR
      (
        M.MASTER_ID = D3.MASTER_ID (+)
        and
        D3.SEQUENCE_NUMBER = 3


      )


        OR
     (
        M.MASTER_ID = D4.MASTER_ID (+)
        and
        D4.SEQUENCE_NUMBER = 4


      )


         OR
(
        M.MASTER_ID = D5.MASTER_ID (+)
        and
        D5.SEQUENCE_NUMBER = 5

    ;

It is simply because the AND predicate between each member only a row is returned when ALL five apply. I'm not sure, but on second thought, I don't see why this select can't be rewritten as
one single join with d.sequence_number (+) between 1 and 5 in the where clause.

Hth,
Sybrand Bakker, Oracle DBA

John Haskins 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?
>
> I'm writing a PL/SQL script that combines data from a MASTER table and a
> DETAIL table and writes compiled information into an OUTPUT table. The
> DETAIL table may have as many as five detail records for each MASTER record.
> I want to read a specific column from each of the matching DETAIL records
> and write the contents of that column from all of the matching DETAIL
> records into just one row in the OUTPUT table. I'm accomplishing this by
> creating five separate joins to the DETAIL table.
>
> This works fine as long as there are five DETAIL records. But when there are
> less than five DETAIL records for a MASTER record, no row is written to the
> OUTPUT table at all.  I would like to have a row written to the OUTPUT table
> even if there are less than five matching DETAIL records. In this case, a
> null value should be placed in any OUTPUT column not populated from a DETAIL
> record.
>
> I tried wrapping a NVL() function around each SELECT statement; that didn't
> work.  Neither did using GREATEST or LEAST functions.
>
> Can anyone tell me how to get a row written into the OUTPUT table even when
> one of the outer joins returns no matching record?
>
> Thanks!
>
> 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
>         M.MASTER_ID,
>         D1.CONSULT_MD    MD_CONS1,
>         D2.CONSULT_MD    MD_CONS2,
>         D3.CONSULT_MD    MD_CONS3,
>         D4.CONSULT_MD    MD_CONS4,
>         D5.CONSULT_MD    MD_CONS5
>     from
>         MASTER_TABLE    M,
>         MASTER_TABLE    D1,
>         MASTER_TABLE    D2,
>         MASTER_TABLE    D3,
>         MASTER_TABLE    D4,
>         MASTER_TABLE    D5
>     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
>     ;
> BEGIN
>     FOR v_sourcerow IN temp_cursor LOOP
>         INSERT INTO OUTPUT_TABLE (
>             MASTER_ID,
>             MD_CONS1,
>             MD_CONS2,
>             MD_CONS3,
>             MD_CONS4,
>             MD_CONS5
>             )
>     VALUES (
>             V_SOURCEROW.MASTER_ID,
>             V_SOURCEROW.MD_CONS1,
>             V_SOURCEROW.MD_CONS2,
>             V_SOURCEROW.MD_CONS3,
>             V_SOURCEROW.MD_CONS4,
>             V_SOURCEROW.MD_CONS5
>             );
>     END LOOP;
> END POPULATE_OUTPUT_TABLE;
> /
> ===============================================