Multi-Group Unpivot in Oracle SQL returns incorrect number of rows

View All Public Scripts Login to Run Script

Script Name Multi-Group Unpivot in Oracle SQL returns incorrect number of rows
Visibility Unlisted - anyone with the share link can access
Description Multi-Group Unpivot in Oracle SQL returns incorrect number of rows
Contributor Oracle
Created Monday July 27, 2020

Statement 1

	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  
	)

Table created.

Statement 2

Insert

Insert

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');

1 row(s) inserted.

Statement 3


	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
	;

Statement 4

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;

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database