Skip to Main Content

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.

Union with Dummy data

580410Nov 14 2007 — edited Nov 16 2007
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.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 14 2007
Added on Nov 14 2007
13 comments
7,443 views