I have the table below:
CREATE TABLE "XXSAMPLE2" ( "JG_INFO_V1" VARCHAR2(240 BYTE), "JG_INFO_V14" VARCHAR2(150 BYTE), "JG_INFO_V16" VARCHAR2(150 BYTE), "JG_INFO_V21" VARCHAR2(150 BYTE), "JG_INFO_V32" VARCHAR2(1996 BYTE), "JG_INFO_N3" NUMBER, "JG_INFO_N4" NUMBER, "JG_INFO_N11" NUMBER, "JG_INFO_N15" NUMBER, "JG_INFO_N30" NUMBER );
and the following records:
Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',3225.79,225.81,842,58.94,'VAT',859634,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',421.42,50.57,110,13.2,'KOREKTA',859635,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',490.38,49.04,128,12.8,'KOREKTA',859635,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',122.6,6.13,32,1.6,'VAT',860621,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',3225.79,225.81,842,58.94,'VAT',860621,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',-100,-10,-100,-10,'KOREKTA',859638,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',-87,-10.44,-87,-10.44,'KOREKTA',859638,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',40,4.4,40,4.4,'KOREKTA',859638,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',-3225.79,-225.81,-842,-58.94,'VAT',860622,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',-122.6,-6.13,-32,-1.6,'VAT',860622,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',367.79,40.46,96,10.56,'KOREKTA',860622,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',3225.79,322.58,842,84.2,'KOREKTA',860622,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',30.06,3.01,30.06,3.01,'KOREKTA',863622,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',74,8.88,74,8.88,'KOREKTA',863622,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',30.06,3.01,30.06,3.01,'KOREKTA',863625,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',74,8.14,74,8.14,'KOREKTA',863625,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',115.16,11.53,30.06,3.01,'KOREKTA',863626,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',283.5,34.02,74,8.88,'KOREKTA',863626,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',-200,-14,-200,-14,'VAT',860619,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',-123,-8.61,-123,-8.61,'VAT',860619,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',-72,-3.6,-72,-3.6,'VAT',860619,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',48,4.8,48,4.8,'KOREKTA',860619,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',200,24,200,24,'KOREKTA',860619,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',369,40.59,369,40.59,'KOREKTA',860619,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('H','Krakowska',null,null,null,null,18,'AV',null,null); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',100,11,100,11,'KOREKTA',859619,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',50,5.5,50,5.5,'KOREKTA',859624,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',144,17.28,144,17.28,'KOREKTA',859624,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',-360.12,-36.01,-94,-9.4,'KOREKTA',859639,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_5',-122.6,-13.49,-32,-3.52,'KOREKTA',859639,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',114.93,13.79,30,3.6,'KOREKTA',859639,'EUR'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',100,7,100,7,'VAT',859618,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',72,3.6,72,3.6,'VAT',859621,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',123,8.61,123,8.61,'VAT',859621,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',200,14,200,14,'VAT',859621,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',32,1.6,32,1.6,'VAT',859626,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',842,58.94,842,58.94,'VAT',859626,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',110,13.2,110,13.2,'KOREKTA',859627,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',128,12.8,128,12.8,'KOREKTA',859627,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',32,1.6,32,1.6,'VAT',859628,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:P_14_1',842,58.94,842,58.94,'VAT',859628,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_3','tns:P_14_3',110,13.2,110,13.2,'KOREKTA',859629,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_4','tns:',128,12.8,128,12.8,'KOREKTA',859629,'PLN'); Insert into XXSAMPLE2 (JG_INFO_V1,JG_INFO_V14,JG_INFO_V16,JG_INFO_N3,JG_INFO_N4,JG_INFO_N15,JG_INFO_N30,JG_INFO_V21,JG_INFO_N11,JG_INFO_V32) values ('AR','tns:P_13_5','tns:P_14_4',122.6,6.13,32,1.6,'VAT',859634,'EUR');
And I’m currently using the query below:
Select invoice_id , Invoice_Type , BOX , functional_box , ROUND(sum(ENTERED_AMT), 2) ENTERED_AMT_TOT , ROUND(sum(FUNC_AMT),2) FUNC_AMT_TOT from ( Select jg_info_v14 box -- taxable_box , Case When jg_info_v14 like 'tns:P_14%' and jg_info_v32 <> 'PLN' Then jg_info_v14||'W' else null end functional_box , jg_info_n3 func_amt -- taxable_amt , jg_info_n15 entered_amt -- entered_taxable_amount , jg_info_v21 Invoice_Type , jg_info_n11 invoice_id , jg_info_v32 invoice_currency_code From XXSAMPLE2 WHERE jg_info_v1 IN ('AR', 'AP') and jg_info_v14 <> 'tns:' union all Select jg_info_v16 box -- tax_box , Case When jg_info_v16 like 'tns:P_14%' and jg_info_v32 <> 'PLN' Then jg_info_v16||'W' else null end functional_box , jg_info_n4 func_amt -- tax_amt , jg_info_n30 entered_amt -- entered_tax_amount , jg_info_v21 Invoice_Type , jg_info_n11 invoice_id , jg_info_v32 invoice_currency_code From XXSAMPLE2 WHERE jg_info_v1 IN ('AR', 'AP') and jg_info_v16 <> 'tns:' ) tax_info WHERE invoice_id = 859639 group by tax_info.invoice_id , tax_info.Invoice_Type , tax_info.BOX , tax_info.functional_box ;
and below are the results:
INVOICE_ID INVOICE_TYPE BOX FUNCTIONAL_BOX ENTERED_AMT_TOT FUNC_AMT_TOT ---------- ------------ ----------- -------------- --------------- ------------- 859639 KOREKTA tns:P_14_3 tns:P_14_3W 3.6 13.79 859639 KOREKTA tns:P_13_5 -32 -122.6 859639 KOREKTA tns:P_13_4 -94 -360.12 859639 KOREKTA tns:P_13_3 30 114.93 859639 KOREKTA tns:P_14_5 tns:P_14_5W -3.52 -13.49
However, I would want to use UNPIVOT
to have the below result:
INVOICE_ID INVOICE_TYPE BOX AMOUNT ---------- ------------ ----------- ------------ 859639 KOREKTA tns:P_14_3 3.6 859639 KOREKTA tns:P_13_5 -32 859639 KOREKTA tns:P_13_4 -94 859639 KOREKTA tns:P_13_3 30 859639 KOREKTA tns:P_14_5 -3.52 859639 KOREKTA tns:P_14_3W 13.79 859639 KOREKTA tns:P_14_5W -13.49
I tried the below query but i am getting incorrect values in the sum:
Select distinct jg_info_n11 invoice_id , jg_info_v21 invoice_type , jg_info_v32 currency_code , TRX_BOX , sum(func_amt) func_amt , sum(entered_amt) entered_amt From XXSAMPLE2 unpivot (TRX_BOX FOR TRX_BOX_VALUES IN (jg_info_v14, jg_info_v16)) unpivot (func_amt FOR func_amt_values IN (jg_info_n3, jg_info_n4)) unpivot (entered_amt FOR entered_amt_values IN (jg_info_n15, jg_info_n30)) WHERE jg_info_v1 IN ('AR', 'AP') and TRX_BOX <> 'tns:' AND jg_info_n11 = 859639 group by jg_info_n11 , jg_info_v21 , jg_info_v32 , TRX_BOX;
below are the results:
INVOICE_ID INVOICE_TYPE CURRENCY_CODE TRX_BOX FUNC_AMT ENTERED_AMT ---------- ------------ -------------- ----------- ----------- --------------- 859639 KOREKTA EUR tns:P_13_4 -792.26 -206.8 859639 KOREKTA EUR tns:P_14_5 -272.18 -71.04 859639 KOREKTA EUR tns:P_13_3 257.44 67.2 859639 KOREKTA EUR tns:P_14_3 257.44 67.2 859639 KOREKTA EUR tns:P_13_5 -272.18 -71.04
If i remove the aggregates, 20 records are being fetched, 4 times the expected results:
INVOICE_ID INVOICE_TYPE CURRENCY_CODE TRX_BOX FUNC_AMT ENTERED_AMT ---------- ------------ -------------- ----------- ----------- --------------- 859639 KOREKTA EUR tns:P_13_4 -36.01 -9.4 859639 KOREKTA EUR tns:P_13_5 -122.6 -3.52 859639 KOREKTA EUR tns:P_14_5 -13.49 -32 859639 KOREKTA EUR tns:P_14_5 -13.49 -3.52 859639 KOREKTA EUR tns:P_13_3 114.93 3.6 859639 KOREKTA EUR tns:P_14_5 -122.6 -3.52 859639 KOREKTA EUR tns:P_13_4 -36.01 -94 859639 KOREKTA EUR tns:P_13_3 114.93 30 859639 KOREKTA EUR tns:P_13_5 -122.6 -32 859639 KOREKTA EUR tns:P_14_3 114.93 30 859639 KOREKTA EUR tns:P_13_4 -360.12 -94 859639 KOREKTA EUR tns:P_13_3 13.79 3.6 859639 KOREKTA EUR tns:P_13_4 -360.12 -9.4 859639 KOREKTA EUR tns:P_13_5 -13.49 -32 859639 KOREKTA EUR tns:P_13_5 -13.49 -3.52 859639 KOREKTA EUR tns:P_14_5 -122.6 -32 859639 KOREKTA EUR tns:P_13_3 13.79 30 859639 KOREKTA EUR tns:P_14_3 114.93 3.6 859639 KOREKTA EUR tns:P_14_3 13.79 30 859639 KOREKTA EUR tns:P_14_3 13.79 3.6
It seems it’s doing a Merge Join Cartesian
with itself:
Plan hash value: 2179312268 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 24 | 28632 | 17 (6)| 00:00:01 | | 1 | HASH UNIQUE | | 24 | 28632 | 17 (6)| 00:00:01 | |* 2 | VIEW | | 24 | 28632 | 16 (0)| 00:00:01 | | 3 | UNPIVOT | | | | | | |* 4 | VIEW | | 12 | 110K| 8 (0)| 00:00:01 | | 5 | UNPIVOT | | | | | | |* 6 | VIEW | | 6 | 56364 | 4 (0)| 00:00:01 | | 7 | UNPIVOT | | | | | | |* 8 | TABLE ACCESS FULL| XXSAMPLE2 | 3 | 28374 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("unpivot_view_025"."ENTERED_AMT" IS NOT NULL) 4 - filter("unpivot_view_013"."FUNC_AMT" IS NOT NULL) 6 - filter("unpivot_view_007"."TRX_BOX" IS NOT NULL AND "unpivot_view_007"."TRX_BOX"<>'tns:') 8 - filter(("XXSAMPLE2"."JG_INFO_V1"='AP' OR "XXSAMPLE2"."JG_INFO_V1"='AR') AND "XXSAMPLE2"."JG_INFO_N11"=859639) Note ----- - dynamic statistics used: dynamic sampling (level=2)
Do I need to Add more conditions to the UNPIVOT
?
Advertisement
Answer
The main issue is that you’re unpivoting three times, and aggregating at the wrong level – aggregation shouldn’t really be necessary at all, at least with the sample data.
You also need to exclude some of the values your current query returns – the functional amounts when functional box is null.
I think this non-union query gets only the data you want:
select jg_info_n11 invoice_id , jg_info_v21 invoice_type , jg_info_v32 currency_code , case when jg_info_v14 = 'tns:' then null else jg_info_v14 end as v14_box , case when jg_info_v14 = 'tns:' then null else jg_info_n15 end as v14_amount , case when jg_info_v14 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_v14||'W' end as v14_func_box , case when jg_info_v14 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_n3 end as v14_func_amount , case when jg_info_v16 = 'tns:' then null else jg_info_v16 end as v16_box , case when jg_info_v16 = 'tns:' then null else jg_info_n30 end as v16_amount , case when jg_info_v16 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_v16||'W' end as v16_func_box , case when jg_info_v16 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_n4 end as v16_func_amount from XXSAMPLE2 where jg_info_v1 IN ('AR', 'AP') and jg_info_n11 = 859639;
which gets:
INVOICE_ID INVOICE_TYPE CURRENCY_CODE V14_BOX V14_AMOUNT V14_FUNC_BOX V14_FUNC_AMOUNT V16_BOX V16_AMOUNT V16_FUNC_BOX V16_FUNC_AMOUNT ---------- ------------ ------------- ---------- ---------- ------------ --------------- ---------- ---------- ------------ --------------- 859639 KOREKTA EUR tns:P_13_4 -94 859639 KOREKTA EUR tns:P_13_5 -32 tns:P_14_5 -3.52 tns:P_14_5W -13.49 859639 KOREKTA EUR tns:P_13_3 30 tns:P_14_3 3.6 tns:P_14_3W 13.79
And you can then unpivot that, with a single command:
select invoice_id, invoice_type, box, amount from ( select jg_info_n11 invoice_id , jg_info_v21 invoice_type , jg_info_v32 currency_code , case when jg_info_v14 = 'tns:' then null else jg_info_v14 end as v14_box , case when jg_info_v14 = 'tns:' then null else jg_info_n15 end as v14_amount , case when jg_info_v14 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_v14||'W' end as v14_func_box , case when jg_info_v14 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_n3 end as v14_func_amount , case when jg_info_v16 = 'tns:' then null else jg_info_v16 end as v16_box , case when jg_info_v16 = 'tns:' then null else jg_info_n30 end as v16_amount , case when jg_info_v16 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_v16||'W' end as v16_func_box , case when jg_info_v16 like 'tns:P_14%' and jg_info_v32 <> 'PLN' then jg_info_n4 end as v16_func_amount from XXSAMPLE2 where jg_info_v1 IN ('AR', 'AP') and jg_info_n11 = 859639 ) unpivot ( (box, amount) for x in ( (v14_box, v14_amount) as 1, (v14_func_box, v14_func_amount) as 2, (v16_box, v16_amount) as 3, (v16_func_box, v16_func_amount) as 4 ) );
which gets:
INVOICE_ID INVOICE_TYPE BOX AMOUNT ---------- ------------ ------------ ---------- 859639 KOREKTA tns:P_13_4 -94 859639 KOREKTA tns:P_13_5 -32 859639 KOREKTA tns:P_14_5 -3.52 859639 KOREKTA tns:P_14_5W -13.49 859639 KOREKTA tns:P_13_3 30 859639 KOREKTA tns:P_14_3 3.6 859639 KOREKTA tns:P_14_3W 13.79