Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Group by rollup query
Problem with Group by rollup query [message #195004] Tue, 26 September 2006 08:26 Go to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
I am having a problem with the group by rollup query. The problem is described just after the query.

--------------------------------------------------------------------------------------------------------------------------

select distinct
GP_IA_TRANSACTION_HEADER.TRANS_NO,
GP_IA_TRANSACTION_detail.TRANS_DTL_LN_NO,
GP_IA_TRANSACTION_HEADER.LAST_UPDATE_DTS,
GP_GP_USER_ROLES.USERNAME,
sum(GP_IA_TRANSACTION_DETAIL.UNIT_COST_AMT) UNIT_COST,
sum(GP_IA_TRANSACTION_DETAIL.UNIT_RTL_AMT) UNIT_RTL,
sum(GP_IA_TRANSACTION_DETAIL.EXT_COST_AMT) EXT_COST,
sum(GP_IA_TRANSACTION_DETAIL.EXT_RTL_AMT) EXT_RTL
from
GP_IA_TRANSACTION_DETAIL,
GP_IA_TRANSACTION_HEADER,GP_GP_USER_ROLES
where GP_IA_TRANSACTION_HEADER.TRANS_NO=GP_IA_TRANSACTION_DETAIL.TRANS_NO
and to_char(GP_GP_USER_ROLES.USER_ID)=GP_IA_TRANSACTION_HEADER.trans_src_id
group by rollup(GP_IA_TRANSACTION_HEADER.TRANS_NO,
(GP_IA_TRANSACTION_HEADER.TRANS_NO,GP_IA_TRANSACTION_detail.TRANS_DTL_LN_NO,GP_IA_TRANSACTION_HEADER.LAST_UPDATE_DTS,
GP_GP_USER_ROLES.USERNAME))
order by GP_IA_TRANSACTION_HEADER.TRANS_NO

-------------------------------------------------------------------------------------------------------------
Result for the above query is given below.

For Eg. below against Transaction Number=1161 we have two rows in
GP_IA_TRANSACTION_DETAIL i.e for TRANS_DTL_LN_NO=1 and TRANS_DTL_LN_NO=2
and the value in EXT_COST_AMT in GP_IA_TRANSACTION_DETAIL against these rows are
-22.71 and 22.71, but the query is returning -227.1 and 227.1 against these values this is happening
when we sum the values and join against table GP_GP_USER_ROLES for getting the username.


This means that it is decreasing by 1 decimal place to the value if the value is 30 it is
returning as 300 and if the value is 32 it is showing 320 (against TRANS_NO=1260 below)

But this does'nt seem to be a cartesian product with GP_GP_USER_ROLES,as the query is returning
the correct number of rows. (1161 has got two rows in GP_IA_TRANSACTION_DETAIL and the following query returns the
rows correctly and the subtotal row (since this is a rollup) for the value = 1161)

I am investigating on this issue, but would appreciate if you find anything wrong with the query, after
testing on your side.

------------------------------------------------------------------------------------------------------------
SQL> /

TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1161 1 20-SEP-06 GP
-227.1 0

1161 2 20-SEP-06 GP
227.1 0

1161
0 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1260 1 20-SEP-06 GP
-320 0

1260 2 20-SEP-06 GP
320 0

1260
0 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1342 1 20-SEP-06 GP
-790 0

1342 2 20-SEP-06 GP
790 0

1342
0 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1343 1 20-SEP-06 GP
-300 0

1343
-300 0

1344 1 20-SEP-06 GP
-2500 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1344
-2500 0

1345 1 21-SEP-06 GP
-340 0

1345 2 21-SEP-06 GP
340 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1345
0 0

1346 1 21-SEP-06 GP
-300 0

1346
-300 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1353 1 22-SEP-06 GP
-780 0

1353
-780 0

1354 1 22-SEP-06 GP
-170 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1354
-170 0

1355 1 22-SEP-06 GP
-130 0

1355
-130 0


TRANS_NO TRANS_DTL_LN_NO LAST_UPDA USERNAME UNIT_COST
---------- --------------- --------- ------------------------------ ----------
UNIT_RTL EXT_COST EXT_RTL
---------- ---------- ----------
1356 1 22-SEP-06 GP
-130 0

1356
-130 0


-4310 0


27 rows selected.
Re: Problem with Group by rollup query [message #195005 is a reply to message #195004] Tue, 26 September 2006 08:31 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
Can anyone help regarding the same.?

Thanks,

Nirmal
Re: Problem with Group by rollup query [message #195008 is a reply to message #195005] Tue, 26 September 2006 08:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Please do not post your query to multiple forums.
Re: Problem with Group by rollup query [message #195010 is a reply to message #195005] Tue, 26 September 2006 08:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Any chance of you postinf the DDL to create these tables?
I've reverse engineered 4 sets of tables from select statements so far today, and to be frank, I think it's time that someone wanting my help did some of the work.
Previous Topic: SQL question
Next Topic: Loading data into CSV files
Goto Forum:
  


Current Time: Sat Dec 10 06:44:05 CST 2016

Total time taken to generate the page: 0.26995 seconds