Forum Stats

  • 3,757,566 Users
  • 2,251,245 Discussions
  • 7,869,867 Comments

Discussions

Union with Dummy data

580410
580410 Member Posts: 8
edited Nov 16, 2007 5:19AM in SQL & PL/SQL
I have a table with 4 fields: customer, item, color, num_purchased

There are 3 items: shirt, shoes and jeans.
And for each item, there are only 2 colors: blue and black

select * from purchase_table

CUSTOMER ITEM COLOR NUM_PURCHASED
john shirt blue 2
john shoes black 1
mary jeans black 1
mary jeans blue 1
mary shirt blue 3
mary shoes blue 2


I want to do add dummy data (0s) to my query using union
select * from purchase_table
union ...

so that my result set is ...

CUSTOMER ITEM COLOR NUM_PURCHASED
john shirt black 0
john shirt blue 2
john shoes black 1
john shoes blue 0
mary jeans black 1
mary jeans blue 1
mary shirt black 0
mary shirt blue 3
mary shoes black 0
mary shoes blue 2

basically.. john purchased a blue shirt, so i want to add 0 black shirts purchased..
mary purchased black and blue jeans, so need to add dummy data
Note: I only want to add dummy data for the items each customer purchased, not for all the items.
Since john didn't purchase jeans, no dummy data for jeans.

Any help appreciated. Thanks.
«1

Comments

  • Sven W.
    Sven W. Member Posts: 10,533 Gold Crown
    Strange requirement. Why should John buy a blue shirt? Black ones look so much better!

    Is this a work requirement or some homework?
  • 580410
    580410 Member Posts: 8
    It's for work. I tried my best to use sample data. :-)
  • Sven W.
    Sven W. Member Posts: 10,533 Gold Crown
    edited Nov 14, 2007 2:29PM
    So you seem to have some kind of data warehouse situation. Maybe some staging area to hold your basis data...

    I'm not sure if I like this "sometimes insert empty rows, sometimes not" approach. There are ways to do something very similar in SQL with ROLLUP or CUBE or GROUPING clauses. Maybe it would be worth looking into those concepts.

    After rereading your original post I definitly think CUBE is the way to go. Can't give you a good example as I'm not so familiar with it, I just know the possibilities.

    Message was edited by:
    Sven Weller
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Hope you are on 10g otherwise following won't work (but it is not too hard too adapt for lower versions too):
    SQL> with purchase_table as 
    (
     select 'john' customer,'shirt' item,'blue' color,2 num_purchased from dual union all
     select 'john','shoes','black',1 from dual union all
     select 'mary','jeans','black',1  from dual union all
     select 'mary','jeans','blue',1  from dual union all
     select 'mary','shirt','blue',3  from dual union all
     select 'mary','shoes','blue',2   from dual
    )
    --
    --
    select customer,
           item,
           nvl(color,c) color,
           nvl(num_purchased,0) num_purchased
      from purchase_table
    partition by (customer,item) right outer join (select decode(level,1,'blue','black') c from dual connect by level <= 2)
              on c = color
    /
    CUST ITEM  COLOR NUM_PURCHASED
    ---- ----- ----- -------------
    john shirt blue              2
    john shirt black             0
    john shoes blue              0
    john shoes black             1
    mary jeans blue              1
    mary jeans black             1
    mary shirt blue              3
    mary shirt black             0
    mary shoes blue              2
    mary shoes black             0
  • 580410
    580410 Member Posts: 8
    I am running 9i and getting this error...

    ORA-00933: SQL command not properly ended
  • MaximDemenko
    MaximDemenko Member Posts: 1,613
    As michaels mentioned, you have slightly adjust his code due to partitioned joins introduced in Oracle 10g
    SQL> select * from v$version
      2  /
    
    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
    PL/SQL Release 9.2.0.8.0 - Production
    CORE    9.2.0.8.0       Production
    TNS for Linux: Version 9.2.0.8.0 - Production
    NLSRTL Version 9.2.0.8.0 - Production
    
    SQL> with purchase_table as
      2  (
      3   select 'john' customer,'shirt' item,'blue' color,2 num_purchased from dual union all
      4   select 'john','shoes','black',1 from dual union all
      5   select 'mary','jeans','black',1  from dual union all
      6   select 'mary','jeans','blue',1  from dual union all
      7   select 'mary','shirt','blue',3  from dual union all
      8   select 'mary','shoes','blue',2   from dual
      9  )
     10  select p.customer,p.item,p.c color,nvl(t.num_purchased,0) num_purchased
     11    from purchase_table t
     12   right outer join (select unique customer, item, c
     13                       from purchase_table,
     14                            (select decode(level, 1, 'blue', 'black') c
     15                               from dual
     16                             connect by level <= 2)) p on t.customer =
     17                                                          p.customer
     18                                                      and t.item = p.item
     19                                                      and t.color = p.c
     20  order by 1,2,3 desc
     21  /
    
    CUST ITEM  COLOR NUM_PURCHASED
    ---- ----- ----- -------------
    john shirt blue              2
    john shirt black             0
    john shoes blue              0
    john shoes black             1
    mary jeans blue              1
    mary jeans black             1
    mary shirt blue              3
    mary shirt black             0
    mary shoes blue              2
    mary shoes black             0
    
    10 rows selected.
    Best regards

    Maxim
  • 580410
    580410 Member Posts: 8
    thank you very much. that worked.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I prefer "Left Join" to "right join".
    Below solution is used "Left Join".
    with purchase_table as (
    select 'john' customer,'shirt' item,'blue' color,2 num from dual union
    select 'john','shoes','black',1 from dual union
    select 'mary','jeans','black',1 from dual union
    select 'mary','jeans','blue', 1 from dual union
    select 'mary','shirt','blue', 3 from dual union
    select 'mary','shoes','blue', 2 from dual)
    select b.CUSTOMER,b.ITEM,a.COLOR,nvl(b.num,0) as NUM
    from (select distinct COLOR from purchase_table) a
    Left join purchase_table b
    partition by (b.CUSTOMER,b.ITEM)
    on (a.COLOR = b.COLOR)
    order by b.CUSTOMER,b.ITEM,a.COLOR desc;
    CUST  ITEM   COLOR  NUM
    ---- ----- ----- ---
    john shirt blue 2
    john shirt black 0
    john shoes blue 0
    john shoes black 1
    mary jeans blue 1
    mary jeans black 1
    mary shirt blue 3
    mary shirt black 0
    mary shoes blue 2
    mary shoes black 0
    On Oracle9i,
    we can use below solution.
    Likewise I prefer "Left Join" to "right join". :-)
    with purchase_table as (
    select 'john' customer,'shirt' item,'blue' color,2 num from dual union
    select 'john','shoes','black',1 from dual union
    select 'mary','jeans','black',1 from dual union
    select 'mary','jeans','blue', 1 from dual union
    select 'mary','shirt','blue', 3 from dual union
    select 'mary','shoes','blue', 2 from dual)
    select a.CUSTOMER,a.ITEM,b.COLOR,nvl(c.num,0) as NUM
    from (select distinct customer,item from purchase_table) a
    cross join (select distinct color from purchase_table) b
    Left Join purchase_table c
    on (a.customer = c.customer
    and a.item = c.item
    and b.color = c.color)
    order by a.CUSTOMER,a.ITEM,b.COLOR desc;
    similar threads
    575147
    584758
  • Sven W.
    Sven W. Member Posts: 10,533 Gold Crown
    I found the following solution, that looks more comprehensible to me:
    with purchase_table as 
        ( select 'john' customer,'shirt' item,'blue' color,2 num from dual union all
    	  select 'john' customer,'shirt' item,'blue' color,3 num from dual union all
    	  select 'john' customer,'shoes' item,'black' color,1 num from dual union all
    	  select 'mary' customer,'jeans' item,'black' color,1 num from dual union all 
    	  select 'mary' customer,'jeans' item,'blue' color, 1 num from dual union all
    	  select 'mary' customer,'shirt' item,'blue' color, 3 num from dual union all
    	  select 'mary' customer,'shoes' item,'blue' color, 2 num from dual)
    SELECT t.customer,t.item, c.color, sum(decode(c.color,t.color,t.num,0)) num_purchased
    FROM purchase_table t
         CROSS JOIN (select distinct color from purchase_table) c 
    GROUP BY t.customer, t.item, c.color
    ORDER BY t.customer, t.item, c.color
    Btw: I didn't find any good solution with CUBE or GROUPING SETS.
  • 584412
    584412 Member Posts: 1,329
    You could even replace CROSS JOIN with a comma ;)
This discussion has been closed.