Pivot query [message #686638] |
Mon, 07 November 2022 22:35 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/cc837b9c65ed1aec0080f5550741ecfe?s=64&d=mm&r=g) |
glmjoy
Messages: 187 Registered: September 2011 Location: KR
|
Senior Member |
|
|
My query is as below which is giving result perfectly. But I want to make dynamic in Pivot query for e.g can I use select deptno in pivot
Sum(sal) for DeptNo in (10,20,30,40) I am doing this which is saying
ERROR at line 6:
ORA-00936: missing expression
select * from (
select nvl(deptno, -1) deptno , job , sum(sal) SAL from scott.emp
group by cube (deptno , job )
)
pivot (
Sum(sal) for DeptNo in (10,20,30,40)
)
order by job
Result
JOB 10 20 30 40
--------- ---------- ---------- ---------- ----------
ANALYST 6000
CLERK 1300 1100 950
MANAGER 2450 3775 2850
PRESIDENT 5000
SALESMAN 1000 5600
9750 10875 9400
---------------------------
But when I use (Select deptno from scott.emp) after
-----------------------------
select * from (
select nvl(deptno, -1) deptno , job , sum(sal) SAL from scott.emp
group by cube (deptno , job )
)
pivot (
Sum(sal) for DeptNo in (Select deptno from scott.emp)
)
order by job
Its give me error
ERROR at line 6:
ORA-00936: missing expression
may be I am doing something wrong
|
|
|
Re: Pivot query [message #686639 is a reply to message #686638] |
Tue, 08 November 2022 00:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68659 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Michel Cadot wrote on Thu, 28 October 2021 07:28
Michel Cadot wrote on Sat, 04 September 2021 16:33Michel Cadot wrote on Wed, 28 July 2021 10:38
Please format your post as explained in How to use [code] tags and make your code easier to read, and align the column in result.
John Watson wrote on Sat, 04 September 2021 09:50You have been asked, more than once, to follow the Forum rules: Format your posts correctly using [code] tags for code, and use type casting functions such as TO_DATE when working with dates and strings.
It us extremely rude of you to ignore these requests.
Your answer:
glmjoy wrote on Sat, 04 September 2021 10:05sorry for that
You are so sorry that you ignore it in your next topic.
...
|
|
|
|
|