Hi guys,
I've got a situation where I'm trying to replace a slow-by-slow procedure with a SQL statement. Seems fairly easy so far, except when it comes to the adding in rows that don't exist.
I've got two tables, t1 holds the data, t2 holds information about the rows that each id in t1 should have. I've managed to get the result I want, but it requires two passes through t1 which I'd like to avoid if at all possible:
with t1 as (select 1 id, 'a' vol, 100 amount from dual union all
select 1 id, 'c' vol, 200 amount from dual union all
select 1 id, 'd' vol, 300 amount from dual union all
select 2 id, 'b' vol, 400 amount from dual),
t2 as (select 'b' vol, 1 order_index from dual union all
select 'd' vol, 2 order_index from dual union all
select 'a' vol, 3 order_index from dual union all
select 'c' vol, 4 order_index from dual)
select t4.id, t4.vol, nvl(t1.amount, 0) amount
from (select t3.id, t2.vol, t2.order_index
from (select distinct id
from t1) t3,
t2) t4,
t1
where t4.id = t1.id (+)
and t4.vol = t1.vol (+)
order by t4.id, t4.order_index;
ID V AMOUNT
---------- - ----------
1 b 0
1 d 300
1 a 100
1 c 200
2 b 400
2 d 0
2 a 0
2 c 0
Anyone got any better ways of doing this, or am I stuck with two passes through t1?