OraFAQ Forum: SQL & PL/SQL » fine tuning my sql query for a grouping report

Home » SQL & PL/SQL » SQL & PL/SQL » fine tuning my sql query for a grouping report (oracle 10g, oracle forms and reports developer suite)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

fine tuning my sql query for a grouping report [message #643649]

Wed, 14 October 2015 23:42

lacchhii
Messages: 151
Registered: May 2009
Location: bangalore

Senior Member

Hi

I have a report running for pendancy in each status for a particular period. I would like to have the report in a different way than how it is now.

I have below the existing query

SELECT DECODE(GROUPING(APPLN_SECN_ID),1,'All SECTIONS',
       APPLN_SECN_ID) AS SECTIONS,
       DECODE ((APPLN_STAT),'02','CLERK','04','ACCT','20','ACCT','25','ACCT','06','AAO','08','AO','ALL STATUS',APPLN_STAT)AS STATUS, 
       COUNT(*) "ALL STATUS"
FROM   T_APPLICATION_HDR ,T_APPLN_PENSIONER
WHERE APPLN_DATE <=:B_DATE
AND   APPLN_STAT NOT IN('30','31','41')
AND   (APEN_SERIES  NOT IN ('4582','4583') OR APEN_SERIES IS NULL)
AND   APPLN_PK=APEN_APPLN_PK
AND   APEN_DOR  NOT IN (SELECT APEN_DOR FROM T_APPLN_PENSIONER
WHERE APPLN_PK=APEN_APPLN_PK
AND APEN_DOR>'31-OCT-2015'--LAST_DAY(SYSDATE) 
AND APPLN_DATE <=:B_DATE)
GROUP BY ROLLUP(APPLN_SECN_ID,DECODE ((APPLN_STAT),'02','CLERK','04','ACCT','20','ACCT','25','ACCT','06','AAO','08','AO','ALL STATUS',APPLN_STAT))

The result of this is like this

SECTIONS STATUS ALL STATUS

PV1 AO 14
PV1 AAO 3
PV1 17

PV2 ACCT 1
PV2 1

PV3 AO 31
PV3 AAO 1
PV3 ACCT 8
PV3 40

PV4 AO 90
PV4 AAO 50
PV4 ACCT 16
PV4 156

PV5 AO 29
PV5 AAO 2
PV5 ACCT 8
PV5 39

I would like to have the report like this

SECTIONS AO AAO ACCT TOTAL

PV1 14 3 17

PV2 1 1

PV3 31 1 8 40

PV4 90 50 16 156

PV5 29 2 8 39

TOTAL 164 56 33 253

I did change the query like this

Quote:

SELECT DECODE(GROUPING(A.APPLN_SECN_ID),1,'All SECTIONS',A.APPLN_SECN_ID) AS SECTIONS,
DECODE ((A.APPLN_STAT),'02','CLERK','04','ACCT','20','ACCT','25','ACCT','06','AAO','08','AO','ALL STATUS',APPLN_STAT)AS STATUS,
C.AO,D.AAO,E.ACCT,F.CLERK,
COUNT(*) "ALL STATUS"
FROM T_APPLICATION_HDR A,
T_APPLN_PENSIONER B,
(SELECT APPLN_SECN_ID,
DECODE ((APPLN_STAT),'08','AO')AS AO,
COUNT(*) "ALL STATUS"
FROM T_APPLICATION_HDR
WHERE APPLN_STAT='08'GROUP BY APPLN_SECN_ID,APPLN_STAT) C,
(SELECT APPLN_SECN_ID,
DECODE ((APPLN_STAT),'06','AAO')AS AAO
FROM T_APPLICATION_HDR
WHERE APPLN_STAT='06'GROUP BY APPLN_SECN_ID,APPLN_STAT) D,
(SELECT APPLN_SECN_ID,
DECODE ((APPLN_STAT),'04','ACCT','20','ACCT','25','ACCT')AS ACCT
FROM T_APPLICATION_HDR
WHERE APPLN_STAT IN ('04','20','25')GROUP BY APPLN_SECN_ID,APPLN_STAT) E,
(SELECT APPLN_SECN_ID,
DECODE ((APPLN_STAT),'02','CLERK')AS CLERK
FROM T_APPLICATION_HDR
WHERE APPLN_STAT='02'GROUP BY APPLN_SECN_ID,APPLN_STAT) F
WHERE A.APPLN_DATE <=:B_DATE
AND A.APPLN_STAT NOT IN('30','31','41')
AND (B.APEN_SERIES NOT IN ('4582','4583') OR APEN_SERIES IS NULL)
AND A.APPLN_PK=B.APEN_APPLN_PK
AND B.APEN_DOR NOT IN (SELECT APEN_DOR FROM T_APPLN_PENSIONER
WHERE APEN_DOR>LAST_DAY(SYSDATE)AND APPLN_PK=APEN_APPLN_PK AND APPLN_DATE <=:B_DATE)
AND A.APPLN_SECN_ID=C.APPLN_SECN_ID
AND A.APPLN_SECN_ID=D.APPLN_SECN_ID
AND A.APPLN_SECN_ID=E.APPLN_SECN_ID
AND A.APPLN_SECN_ID=F.APPLN_SECN_ID
GROUP BY ROLLUP(A.APPLN_SECN_ID,DECODE ((A.APPLN_STAT),'02','CLERK','04','ACCT','20','ACCT','25','ACCT','06','AAO','08','AO','ALL STATUS',A.APPLN_STAT),C.AO,D.AAO,E.ACCT,F.CLERK)

The result comes like this and also the total count are doubled the actuals

SECTIONS AO AAO ACCT CLERK ALL STATUS

PV1 AO AAO ACCT 6
PV1 AO AAO 6
PV1 AO 6
PV1 6
PV1 34

PV2 AO AAO ACCT CLERK 2
PV2 AO AAO ACCT 2
PV2 AO AAO 2
PV2 AO 2
PV2 2
PV2 2

I require help in refining the query to get the report as mentioned above

thanks in advance

Report message to a moderator

Re: fine tuning my sql query for a grouping report [message #643651 is a reply to message #643649]

Thu, 15 October 2015 01:25

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
If you don't know how to format a query it, learn it using SQL Formatter.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Previous Topic:	questions about creating function
Next Topic:	split data with levels

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 13 20:35:50 CDT 2026