I have a large refcursor that is currently being returned from a oracle 11g stored procedure. I need to add a few addiotional columns to the record set before it is returned. The logic to include the new columns is not simple and I will have to WITH statements to store the new columns. In total, I will have the WITH containing the results that are currently being returned and 5 additional WITH statements that each contain an id column to join to the existing record set and the new column. My question is, how would I left outer join the existing record set(with id column) with each of the 5 WITH statement queries to include the 5 new column in the original dataset?
example:
WITH orginalresults AS (
SELECT id,
col1,
col2
...
)
WITH newcol1 AS (
Select id,
newcol1
)
WITH newcol2 AS (
select id,
newcol2
)
Join
select .*, newcol1.newcol1, newcol2.newcol2, newcol3.newcol3,newcol4.newcol4 .....
from orginalresults
left outer join newcol1 on orginalresults .id = newcol1.id
left outer join newcol2 on originalresults.id = newcol2.id
left outer join newcol3 on originalresults.id = newcol2.id
left outer join newcol4 on originalresults.id = newcol4.id
.....
I will have to repeat the joins five times. Is this approach valid? Is there a better way to do this?