Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Adding in made-up rows - looking for more efficient solutions

BoneistJun 19 2009 — edited Jun 19 2009
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?
This post has been answered by 21205 on Jun 19 2009
Jump to Answer

Comments

21205
Answer
I think this will do the same 
SQL> with t1 as (select 1 id, 'a' vol, 100 amount from dual union all
  2              select 1 id, 'c' vol, 200 amount from dual union all
  3              select 1 id, 'd' vol, 300 amount from dual union all
  4              select 2 id, 'b' vol, 400 amount from dual),
  5       t2 as (select 'b' vol, 1 order_index from dual union all
  6              select 'd' vol, 2 order_index from dual union all
  7              select 'a' vol, 3 order_index from dual union all
  8              select 'c' vol, 4 order_index from dual)
  9  select t1.id
 10      , t2.vol
 11      , nvl (t1.amount, 0)
 12      , t2.order_index
 13    from t1
 14        partition by (id)
 15        right outer
 16        join t2
 17        on (t1.vol = t2.vol)
 18  order by t1.id, order_index
 19  /

        ID V NVL(T1.AMOUNT,0) ORDER_INDEX
---------- - ---------------- -----------
         1 b                0           1
         1 d              300           2
         1 a              100           3
         1 c              200           4
         2 b              400           1
         2 d                0           2
         2 a                0           3
         2 c                0           4

8 rows selected.
(if you're on Oracle 10)

missed the final ORDER BY

Edited by: Alex Nuijten on Jun 19, 2009 10:23 AM
Marked as Answer by Boneist · Sep 27 2020
Boneist
Ooh, that's very nifty!

Thanks... guess I'm going to have to get past my aversion to the ANSI join syntax, aren't I?! That's a very useful trick, and one I wasn't aware of.
21205
:)

and here's the Tahiti link: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2177515

(to up your Tahiti-Fu skills ;) )
1 person found this helpful
Nicolas Gasparotto
What about code from old school time ? 
SQL> with t1 as (select 1 id, 'a' vol, 100 amount from dual union all
  2              select 1 id, 'c' vol, 200 amount from dual union all
  3              select 1 id, 'd' vol, 300 amount from dual union all
  4              select 2 id, 'b' vol, 400 amount from dual),
  5       t2 as (select 'b' vol, 1 order_index from dual union all
  6              select 'd' vol, 2 order_index from dual union all
  7              select 'a' vol, 3 order_index from dual union all
  8              select 'c' vol, 4 order_index from dual)
  9  select a.id, b.vol,sum(decode(a.vol,b.vol,a.amount,0)) amount
 10  from   t1 a, t2 b
 11  group by a.id, b.vol,b.order_index
 12  order by a.id, b.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

8 rows selected.
Nicolas.
1 person found this helpful
Aketi Jyuuzou
575147

I like Left Join better than Right Join ;-)
1 person found this helpful
21205
Aketi Jyuuzou wrote:
I like Left Join better than Right Join ;-)
... we aim to please: 
SQL> with t1 as (select 1 id, 'a' vol, 100 amount from dual union all
  2              select 1 id, 'c' vol, 200 amount from dual union all
  3              select 1 id, 'd' vol, 300 amount from dual union all
  4              select 2 id, 'b' vol, 400 amount from dual),
  5       t2 as (select 'b' vol, 1 order_index from dual union all
  6              select 'd' vol, 2 order_index from dual union all
  7              select 'a' vol, 3 order_index from dual union all
  8              select 'c' vol, 4 order_index from dual)
  9  select t1.id
 10      , t2.vol
 11      , nvl (t1.amount, 0)
 12      , t2.order_index
 13    from t2 left outer
 14        join
 15        t1
 16        partition by (id)
 17        on (t1.vol = t2.vol)
 18  order by t1.id, order_index
 19  /

        ID V NVL(T1.AMOUNT,0) ORDER_INDEX
---------- - ---------------- -----------
         1 b                0           1
         1 d              300           2
         1 a              100           3
         1 c              200           4
         2 b              400           1
         2 d                0           2
         2 a                0           3
         2 c                0           4

8 rows selected.
;)
1 person found this helpful
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 17 2009
Added on Jun 19 2009
6 comments
794 views