| using decode funtion with group by rollup [message #479582] |
Mon, 18 October 2010 07:33  |
240130
Messages: 10
Registered: October 2010
|
Junior Member |
|
|
Hi,
I am trying to use decode funtion with GROUP BY ROLLUP
MY query is working fine when i use this two queris individually
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ((DEPTNO),(DEPTNO,JOB));
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP((JOB),(DEPTNO,JOB));
But when i use Decode funtion so that i can combine above two scenarios it is not working
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ( DECODE(:A,'S',((DEPTNO),(DEPTNO,JOB) ),((JOB),(DEPTNO,JOB) ) ) )
Thanks in advance
|
|
|
|
|
|
| Re: using decode funtion with group by rollup [message #479718 is a reply to message #479633] |
Mon, 18 October 2010 18:36  |
 |
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I can't find any way to use decode in the middle of a rollup clause. The following is the closest workaround that I can think of.
SCOTT@orcl_11gR2> VARIABLE A VARCHAR2
SCOTT@orcl_11gR2> EXEC :A := 'S'
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> SELECT sum_sal, deptno, job
2 FROM (SELECT SUM (SAL) sum_sal, DEPTNO, JOB, 'S' deptno_or_job
3 FROM EMP
4 GROUP BY ROLLUP ((DEPTNO), (DEPTNO, JOB))
5 UNION ALL
6 SELECT SUM (SAL) sum_sal, DEPTNO, JOB, 'T' deptno_or_job
7 FROM EMP
8 GROUP BY ROLLUP ((JOB), (DEPTNO, JOB)))
9 WHERE deptno_or_job = :A
10 /
SUM_SAL DEPTNO JOB
---------- ---------- ---------
1300 10 CLERK
2450 10 MANAGER
5000 10 PRESIDENT
8750 10
1900 20 CLERK
6000 20 ANALYST
2975 20 MANAGER
10875 20
950 30 CLERK
2850 30 MANAGER
5600 30 SALESMAN
9400 30
29025
13 rows selected.
SCOTT@orcl_11gR2> EXEC :A := 'T'
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> /
SUM_SAL DEPTNO JOB
---------- ---------- ---------
1300 10 CLERK
1900 20 CLERK
950 30 CLERK
4150 CLERK
6000 20 ANALYST
6000 ANALYST
2450 10 MANAGER
2975 20 MANAGER
2850 30 MANAGER
8275 MANAGER
5600 30 SALESMAN
5600 SALESMAN
5000 10 PRESIDENT
5000 PRESIDENT
29025
15 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: using decode funtion with group by rollup [message #479727 is a reply to message #479718] |
Tue, 19 October 2010 00:37 |
240130
Messages: 10
Registered: October 2010
|
Junior Member |
|
|
Thanks Barbar. 
This is what i wanted.It is working fine now.
Is there any other way apart from the one which you have given solution.
Because i am using this logic in some other tables,though it working fine now my query has become very big.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
