PLSQL: Outer Joins omit some records - how to fix?

From: John Haskins <76054.334_at_compuserve.com>
Date: Sun, 17 Jan 1999 14:11:24 -0800
Message-ID: <77tmsp$84b$1_at_news-1.news.gte.net>



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;
/
Received on Sun Jan 17 1999 - 23:11:24 CET

Original text of this message