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

Looking at it rather quickly, I think your problem is that you need outer join symbols on the detail sequence numbers also, e.g. D2.SEQUENCE_NUMBER (+) = 1, etc.

Normally, when you outer join, your "where" clause needs to have an outer join symbol on every column that is referenced from the table being outer joined.

Try this. I would be interested in hearing of your results.

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;
> /
> ===============================================
Received on Mon Jan 18 1999 - 18:26:27 CET