Home » SQL & PL/SQL » SQL & PL/SQL » Pivot query (19c)
Pivot query [message #686638] Mon, 07 November 2022 22:35 Go to next message
glmjoy
Messages: 184
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 messageGo to next message
Michel Cadot
Messages: 68348
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:33
Michel 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:50
You 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:05
sorry for that
You are so sorry that you ignore it in your next topic.

...
Re: Pivot query [message #686640 is a reply to message #686638] Tue, 08 November 2022 08:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3201
Registered: January 2010
Location: Connecticut, USA
Senior Member
Apparently you don't read documentation.

SY.
Re: Pivot query [message #686641 is a reply to message #686640] Tue, 08 November 2022 12:42 Go to previous message
glmjoy
Messages: 184
Registered: September 2011
Location: KR
Senior Member
Yes I didnt read documentation
Previous Topic: INSERT SELECT not working
Next Topic: JOIN with cte
Goto Forum:
  


Current Time: Sun Feb 05 01:51:48 CST 2023