PLSQL: Outer Joins omit some records - how to fix?
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