Hi , I have following tables . I am getting orders data from various sources in table test_orders. Then joining the items to various table for revenue percentage and then send the orders along with revenue information to another 3rd party tool for revenue recognition.
My goal is to have outbound_rev_orders table populated as given below using insert statements. but before that I need to get Item child information
if item child is present in following order :
1) single_items
2) parent_child_items_set1
3) product_child_items
If item is present only in table then , I can right a quesry using outer join and achieve the results in a single query. But here few items are present in parent_child_items_set1 and product_child_items tables. In such cases , I want to select details only from parent_child_items_set1 and ignore data from product_child_items . How do I achieve this using single SQL query. I know in pl/sql it is possible to achieve that but it's get little complicated , so exploring if single SQL query can do this ?
---drop table test_orders;
create table test_orders ( order_id number , item_number varchar(25), rev_acct_number number, unit_sell_price number , qty number ) ;
insert into test_orders values ( 100, 'ITEM-A', 1001 , 250, 2 );
insert into test_orders values ( 100, 'ITEM-B', 1001 , 500, 10 );
insert into test_orders values ( 100, 'ITEM-C', 1001 , 100, 10 );
insert into test_orders values ( 100, 'ITEM-D', 2222 , 300, 10 );
insert into test_orders values ( 100, 'ITEM-E', 2001 , 200, 20 );
--- Item tables
---drop table single_items
create table single_items ( item_number varchar2(25) , rev_acct_number number , attribute1 varchar2(25) );
insert into single_items values ( 'ITEM-A', 1001, 'REV ALLOCATION A' );
--- drop table parent_child_items_set1
create table parent_child_items_set1 ( item_number varchar2(25) , rev_acct_number number ,item_child varchar2(25), attribute1 varchar2(25), rev_prct number );
insert into parent_child_items_set1 values ( 'ITEM-B', 1001, 'ITEM-B-CHILD1', 'REV ALLOCATION B1', 0.25 );
insert into parent_child_items_set1 values ( 'ITEM-B', 1002, 'ITEM-B-CHILD2', 'REV ALLOCATION b2', 0.75 );
insert into parent_child_items_set1 values ( 'ITEM-C', 1001, 'ITEM-C-CHILD1', 'REV ALLOCATION C1', 0.45 );
insert into parent_child_items_set1 values ( 'ITEM-C', 1002, 'ITEM-C-CHILD2', 'REV ALLOCATION C2', 0.55 );
--- drop table product_child_items;
create table product_child_items ( item_number varchar2(25) , rev_acct_number number ,item_child varchar2(25), attribute1 varchar2(25), rev_prct number );
insert into product_child_items values ( 'ITEM-C', 1001, 'ITEM-C-CHILD1', 'REV ALLOCATION C1', 0.30 );
insert into product_child_items values ( 'ITEM-C', 1002, 'ITEM-C-CHILD2', 'REV ALLOCATION C2', 0.35 );
insert into product_child_items values ( 'ITEM-C', 1003, 'ITEM-C-CHILD3', 'REV ALLOCATION C3', 0.35 );
insert into product_child_items values ( 'ITEM-E', 2001, 'ITEM-E-CHILD1', 'REV ALLOCATION E1', 0.50 );
insert into product_child_items values ( 'ITEM-E', 2002, 'ITEM-E-CHILD2', 'REV ALLOCATION E2', 0.50 );
----drop table outbound_rev_orders
create table outbound_rev_orders ( order_id number , item_number varchar(25), item_child varchar2(25), attribute1 varchar2(25), rev_acct_number number,
unit_sell_price number , qty number, rev_prct number ) ;
insert into outbound_rev_orders values ( 100, 'ITEM-A', null, 'REV ALLOCATION A', 1001 , 250, 2, null );
insert into outbound_rev_orders values ( 100, 'ITEM-B', 'ITEM-B-CHILD1','REV ALLOCATION B1', 1001 , 500, 10, 0.25 );
insert into outbound_rev_orders values ( 100, 'ITEM-B', 'ITEM-B-CHILD2','REV ALLOCATION B2', 1002 , 500, 10, 0.75 );
insert into outbound_rev_orders values ( 100, 'ITEM-C', 'ITEM-C-CHILD1','REV ALLOCATION C1', 1001 , 500, 10, 0.30 );
insert into outbound_rev_orders values ( 100, 'ITEM-C', 'ITEM-C-CHILD2','REV ALLOCATION C2', 1002 , 500, 10, 0.35 );
insert into outbound_rev_orders values ( 100, 'ITEM-C', 'ITEM-C-CHILD3','REV ALLOCATION C3', 1003 , 500, 10, 0.35 );
insert into outbound_rev_orders values ( 100, 'ITEM-D', null ,null, 1003 , 500, 10, null );
insert into outbound_rev_orders values ( 100, 'ITEM-E', 'ITEM-E-CHILD1', 'REV ALLOCATION E1', 1003 , 500, 10, 0.5 );
insert into outbound_rev_orders values ( 100, 'ITEM-E', 'ITEM-E-CHILD2', 'REV ALLOCATION E2', 1003 , 500, 10, 0.5 );
commit;
Thanks
Anu
If you want to select columns from different tables with a priority, you can:
- (full) outer join all the tables
- Use coalesce to return the first non-null column from these tables in the desired order
Plugging this into your data gives something like:
select tor.order_id,
coalesce ( sit.item_number, pcis.item_number, pci.item_number, tor.item_number) item,
coalesce ( sit.rev_acct_number, pcis.rev_acct_number, pci.rev_acct_number, tor.rev_acct_number) acct,
coalesce ( sit.attribute1, pcis.attribute1, pci.attribute1) attr1,
coalesce ( pcis.rev_prct, pci.rev_prct) prct
from test_orders tor
full join single_items sit
on tor.item_number = sit.item_number
and sit.rev_acct_number = tor.rev_acct_number
full join parent_child_items_set1 pcis
on pcis.item_number = tor.item_number
and pcis.rev_acct_number = tor.rev_acct_number
full join product_child_items pci
on pci.item_number = tor.item_number
and pci.rev_acct_number = tor.rev_acct_number
order by item, acct;
ORDER_ID ITEM ACCT ATTR1 PRCT
100 ITEM-A 1001 REV ALLOCATION A
100 ITEM-B 1001 REV ALLOCATION B1 0.25
ITEM-B 1002 REV ALLOCATION b2 0.75
100 ITEM-C 1001 REV ALLOCATION C1 0.45
ITEM-C 1002 REV ALLOCATION C2 0.35
ITEM-C 1002 REV ALLOCATION C2 0.55
ITEM-C 1003 REV ALLOCATION C3 0.35
100 ITEM-D 2222
100 ITEM-E 2001 REV ALLOCATION E1 0.5
ITEM-E 2002 REV ALLOCATION E2 0.5This has doubled up ITEM-C, acct 1002. I'm not sure exactly how your tables are joined, so it may be a mistake there. If the joins are correct, you can remove the extra rows by:
- Assigning a row_number for each item and acct, based on the order source table and the order you want to prioritise these
- Selecting the first row_number in each group
You can use coalesce again to ensure you partition by item and acct. And map the tables to a number matching your priority order in the order by clause.
For example:
row_number() over (
partition by
coalesce ( sit.item_number, pcis.item_number, pci.item_number, tor.item_number),
coalesce ( sit.rev_acct_number, pcis.rev_acct_number, pci.rev_acct_number, tor.rev_acct_number)
order by coalesce (
case when sit.item_number is not null then 1 end,
case when pcis.item_number is not null then 2 end,
case when pci.item_number is not null then 3 end
)
)Put it all together and you get:
with rws as (
select tor.order_id,
coalesce ( sit.item_number, pcis.item_number, pci.item_number, tor.item_number) item,
coalesce ( sit.rev_acct_number, pcis.rev_acct_number, pci.rev_acct_number, tor.rev_acct_number) acct,
coalesce ( sit.attribute1, pcis.attribute1, pci.attribute1) attr1,
coalesce ( pcis.rev_prct, pci.rev_prct) prct,
row_number() over (
partition by
coalesce ( sit.item_number, pcis.item_number, pci.item_number, tor.item_number),
coalesce ( sit.rev_acct_number, pcis.rev_acct_number, pci.rev_acct_number, tor.rev_acct_number)
order by coalesce (
case when sit.item_number is not null then 1 end,
case when pcis.item_number is not null then 2 end,
case when pci.item_number is not null then 3 end
)
) rn
from test_orders tor
full join single_items sit
on tor.item_number = sit.item_number
and sit.rev_acct_number = tor.rev_acct_number
full join parent_child_items_set1 pcis
on pcis.item_number = tor.item_number
and pcis.rev_acct_number = tor.rev_acct_number
full join product_child_items pci
on pci.item_number = tor.item_number
and pci.rev_acct_number = tor.rev_acct_number
)
select * from rws
where rn = 1
order by item, acct;
ORDER_ID ITEM ACCT ATTR1 PRCT RN
100 ITEM-A 1001 REV ALLOCATION A 1
100 ITEM-B 1001 REV ALLOCATION B1 0.25 1
ITEM-B 1002 REV ALLOCATION b2 0.75 1
100 ITEM-C 1001 REV ALLOCATION C1 0.45 1
ITEM-C 1002 REV ALLOCATION C2 0.55 1
ITEM-C 1003 REV ALLOCATION C3 0.35 1
100 ITEM-D 2222 1
100 ITEM-E 2001 REV ALLOCATION E1 0.5 1
ITEM-E 2002 REV ALLOCATION E2 0.5 1This still leaves you with some blank order_ids to "fill down". I'm not sure what's supposed to happen if an item appears on multiple orders, so I'll leave this as an exercise for the reader ;)