SQL & PL/SQL

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?