Hi Team,
Could you please have a look at below use case and help to form SQL/PLSQL using which I can get the below report..
Table: order_country : holds order id and country its belong.
There can be 100 and more countries in that but for sample case I have added only 2 india & china here.
For each country there is a separate database table like tab_india, tab_china, tab_us, tab_uk, and so on..
Each table can have different set of columns and can have 100, 200... columns (for sample test i have added only 4 columns here).
I need to get the result -
There will be order_id's as an input (it can be 100 or more)..
Using order_id, get the country from order_country
Then for each country, query the respective table like for 1001-india table would be tab_india, 1002-china table would be tab_china, etc.
Idea is to get coulmns (excluding first 4 columns order_id, order_dt, order_sub_id, order_type) having value as 1 WHERE order_type = 'U' and the count for which particular column is updated for a guven order_id and order_type = 'U'.
i.e. we would search value 1
-- in columns low_value_ind, high_value_ind, credit_value_ind, debit_value_ind for tab_india and
-- in columns lvl_indicator, hgh_indicator, ccy_ind, amt_ind for tab_china etc
Finally below data set to be passed to Frontend..
First 4 columns of each table are used in WHERE to match order_id, order_type, order_dt
NOTE: In each table, we need to check only for order_type = 'U'
Expected result:
ORDER_ID ORDER_DT COLUMNS_UPDATED UPDATED_COUNT
-------- -------- --------------- -------------
1001 28-02-18 high_value_ind 3
1001 28-02-18 credit_value_ind 1
1001 28-02-18 debit_value_ind 2
-- Column: low_value_ind will not be considered for 1001 and tab_india as its not contain value 1 for order_type = 'U'
1002 28-02-18 lvl_indicator 3
1002 28-02-18 hgh_indicator 3
1002 28-02-18 amt_ind 2
-- Column: ccy_ind will not be considered for 1002 and tab_china as its not contain value 1 for order_type = 'U'
-- Table structure and sample data..
DROP TABLE order_country PURGE;
DROP TABLE tab_india PURGE;
DROP TABLE tab_china PURGE;
CREATE TABLE order_country
(order_id number(4),
country varchar(50));
INSERT INTO order_country
VALUES (1001, 'india');
INSERT INTO order_country
VALUES (1002, 'china');
COMMIT;
-- tab_india --
CREATE TABLE tab_india
(order_id number(4),
order_dt date,
order_sub_id number(4),
order_type VARCHAR2(1),
low_value_ind number(1),
high_value_ind number(1),
credit_value_ind number(1),
debit_value_ind number(1));
INSERT INTO tab_india
(order_id, order_dt, order_sub_id, order_type, low_value_ind, high_value_ind, credit_value_ind, debit_value_ind)
VALUES
(1001, TO_DATE('28/02/2018','DD/MM/YYYY'), 1, 'I', 1, null, null,1);
INSERT INTO tab_india
(order_id, order_dt, order_sub_id, order_type, low_value_ind, high_value_ind, credit_value_ind, debit_value_ind)
VALUES
(1001, TO_DATE('28/02/2018','DD/MM/YYYY'), 1, 'D', null, 1, null,1);
INSERT INTO tab_india
(order_id, order_dt, order_sub_id, order_type, low_value_ind, high_value_ind, credit_value_ind, debit_value_ind)
VALUES
(1001, TO_DATE('28/02/2018','DD/MM/YYYY'), 2, 'U', null, 1, null,1);
INSERT INTO tab_india
(order_id, order_dt, order_sub_id, order_type, low_value_ind, high_value_ind, credit_value_ind, debit_value_ind)
VALUES
(1001, TO_DATE('28/02/2018','DD/MM/YYYY'), 2, 'U', null, 1, 1, 1);
INSERT INTO tab_india
(order_id, order_dt, order_sub_id, order_type, low_value_ind, high_value_ind, credit_value_ind, debit_value_ind)
VALUES
(1001, TO_DATE('28/02/2018','DD/MM/YYYY'), 2, 'U', null, 1, null, null);
COMMIT;
-- tab_china --
CREATE TABLE tab_china
(order_id number(4),
order_dt date,
order_sub_id number(4),
order_type VARCHAR2(1),
lvl_indicator number(1),
hgh_indicator number(1),
ccy_ind number(1),
amt_ind number(1));
INSERT INTO tab_china
(order_id, order_dt, order_sub_id, order_type, lvl_indicator, hgh_indicator, ccy_ind, amt_ind)
VALUES
(1002, TO_DATE('28/02/2018','DD/MM/YYYY'), 1, 'I', 1, null, null,1);
INSERT INTO tab_china
(order_id, order_dt, order_sub_id, order_type, lvl_indicator, hgh_indicator, ccy_ind, amt_ind)
VALUES
(1002, TO_DATE('28/02/2018','DD/MM/YYYY'), 1, 'D', 1, 1, null,1);
INSERT INTO tab_china
(order_id, order_dt, order_sub_id, order_type, lvl_indicator, hgh_indicator, ccy_ind, amt_ind)
VALUES
(1002, TO_DATE('28/02/2018','DD/MM/YYYY'), 2, 'U', 1, 1, null,1);
INSERT INTO tab_china
(order_id, order_dt, order_sub_id, order_type, lvl_indicator, hgh_indicator, ccy_ind, amt_ind)
VALUES
(1002, TO_DATE('28/02/2018','DD/MM/YYYY'), 2, 'U', 1, 1, null, 1);
INSERT INTO tab_china
(order_id, order_dt, order_sub_id, order_type, lvl_indicator, hgh_indicator, ccy_ind, amt_ind)
VALUES
(1002, TO_DATE('28/02/2018','DD/MM/YYYY'), 2, 'U', 1, 1, null, null);
COMMIT;
Ug. That's an ugly database...
Anyway, all you need to do is:
- Union all the country tables together
- For each, select null for the columns that don't exist in it but are in others
- Unpivot the results of this
This will automatically exclude the null valued columns:
with all_values as (
select order_id , order_dt ,
low_value_ind , high_value_ind , credit_value_ind , debit_value_ind ,
null lvl_indicator , null hgh_indicator , null ccy_ind , null amt_ind
from tab_india
where order_type = 'U'
union all
select order_id , order_dt ,
null low_value_ind , null high_value_ind ,
null credit_value_ind , null debit_value_ind ,
lvl_indicator , hgh_indicator , ccy_ind , amt_ind
from tab_china
where order_type = 'U'
), rws as (
select * from all_values
unpivot (
col for src in (
low_value_ind,
high_value_ind,
credit_value_ind,
debit_value_ind,
lvl_indicator ,
hgh_indicator ,
ccy_ind ,
amt_ind
)
)
)
select order_id, order_dt, src, count(*) from rws
group by order_id, order_dt, src
order by 1, 2, 3;
ORDER_ID ORDER_DT SRC COUNT(*)
1001 28-FEB-2018 00:00:00 CREDIT_VALUE_IND 1
1001 28-FEB-2018 00:00:00 DEBIT_VALUE_IND 2
1001 28-FEB-2018 00:00:00 HIGH_VALUE_IND 3
1002 28-FEB-2018 00:00:00 AMT_IND 2
1002 28-FEB-2018 00:00:00 HGH_INDICATOR 3
1002 28-FEB-2018 00:00:00 LVL_INDICATOR 3