| Problem with Group by rollup query [message #195004] |
Tue, 26 September 2006 08:26  |
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 #195010 is a reply to message #195005] |
Tue, 26 September 2006 08:46  |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
