Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 11g Pivot for Subquery (Oracle 11g,Win XP)
Oracle 11g Pivot for Subquery [message #399407] Wed, 22 April 2009 02:13 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

Please find the following query, can you please suggest me any alternate for using sub query in Pivot ? Can we use Subquery in pivot or not ?

select * from (
SELECT 
 calendar_year_month_nm AS MONTHYEAR,
 DCS.CASE_STATUS_GROUP_CD AS CASESTATUS,
   FCT.CASE_CNT_1
FROM   EDW_DEV_1.F_IHSS_CASE_LIFECYCLE_EVENTS FCT
 INNER JOIN EDW_DEV_1.D_DATE DT ON DT.DATE_SK = FCT.DATE_SK
 INNER JOIN EDW_DEV_1.D_CASE_STATUS DCS ON DCS.CASE_STATUS_CD_NK = FCT.CASE_STATUS_CD_NK
WHERE DT.FIRST_DAY_IN_MONTH_DT>=ADD_MONTHS(SYSDATE,-13) AND DT.LAST_DAY_IN_MONTH_DT<=SYSDATE
)
 PIVOT
 (SUM(CASE_CNT_1) 
 for MONTHYEAR in 
 (    
 --SELECT distinct calendar_year_month_nm from D_DATE
 'Apr08','Jun08','Jul08' 
) 
 );


This is the code change which i need to do... When i include the Select Statement, i am getting "Missing Expression" Error
Quote:

PIVOT
(SUM(CASE_CNT_1)
for MONTHYEAR in
(
--SELECT distinct calendar_year_month_nm from D_DATE
'Apr08','Jun08','Jul08'
)



Regards,
Ashoka BL
Re: Oracle 11g Pivot for Subquery [message #399408 is a reply to message #399407] Wed, 22 April 2009 02:20 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi All,

I have checked in the following URL,
http://forums.oracle.com/forums/thread.jspa?threadID=692496

It says that Subquery is not allowed in Pivot, but i will get the results dynamically (so cant hardcode it). Can we have any alternative?

Regards,
Ashoka BL
Re: Oracle 11g Pivot for Subquery [message #399414 is a reply to message #399408] Wed, 22 April 2009 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has been asked many times, search for "pivot".

Regards
Michel
Re: Oracle 11g Pivot for Subquery [message #399425 is a reply to message #399408] Wed, 22 April 2009 03:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The Documentation says that you can have a subbquery in a PIVOT statement.

Possibly it's because you're refering to the column alias in the PIVOT rather than the column name?

Post a create table and some data and we'll (almost certainly) get it working.
Re: Oracle 11g Pivot for Subquery [message #399435 is a reply to message #399407] Wed, 22 April 2009 03:18 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@Michel,

I know that it is for PIVOT, but what i asked is can we have an alternate so that i can use Select statement in Pivot Clause,

Meanwhile i will search as well.

Regards,
Ashoka BL
Re: Oracle 11g Pivot for Subquery [message #399436 is a reply to message #399435] Wed, 22 April 2009 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JRowbottom wrote on Wed, 22 April 2009 10:02
...
Post a create table and some data and we'll (almost certainly) get it working.

Re: Oracle 11g Pivot for Subquery [message #399447 is a reply to message #399407] Wed, 22 April 2009 04:51 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@Jrowbottom,

I didn't see your comments,

Actually its a DW(DatawareHouse ETL) Project, so the tables contains huge data(close to Million Records) and even the DDL script is very big,

Can you suggest any modifications which i can do, I will try and let you know.

I know that i have to put DDL/DML to get it going, but i am really sorry.

I need to know how do i use the SQL Subquery in Pivot clause

Regards,
Ashoka BL
Re: Oracle 11g Pivot for Subquery [message #399449 is a reply to message #399447] Wed, 22 April 2009 04:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We don't need all the columns, and we don't need all the data.
All we need are the columns referenced in the query, and enough data to demonstrate what the results you're looking for are.

Also - a million records isn't very many - I've created test cases with that many record in them.
Re: Oracle 11g Pivot for Subquery [message #399793 is a reply to message #399407] Fri, 24 April 2009 01:56 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@JRowBottom,

Sorry for the late reply..

Please use a SCOTT.EMP Table,
CREATE TABLE emp
( empno    NUMBER(4,0),
  ename    VARCHAR2(10 BYTE),
  job      VARCHAR2(9 BYTE),
  mgr      NUMBER(4,0),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2,0)
); 

I need to put a Sub Query here, instead of
Quote:

(10 AS ACCOUNTING ,20 AS RESEARCH,30 AS SALES,40 AS OPERATIONS))

SELECT *
FROM (SELECT deptno,sal
      FROM emp
      WHERE deptno > 0
)
PIVOT (SUM(sal) AS SUM_AMOUNT FOR (deptno) 
IN (10 AS ACCOUNTING ,20 AS RESEARCH,30 AS SALES,40 AS OPERATIONS));

Please let me know how to proceed,

[Updated on: Fri, 24 April 2009 03:17] by Moderator

Report message to a moderator

Re: Oracle 11g Pivot for Subquery [message #399806 is a reply to message #399793] Fri, 24 April 2009 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.

Regards
Michel
Re: Oracle 11g Pivot for Subquery [message #399807 is a reply to message #399407] Fri, 24 April 2009 03:31 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@Michel,

Can you suggest me an alternative for this, i want to still use PIVOT statement.

Regards,
Ashoka BL
Re: Oracle 11g Pivot for Subquery [message #399808 is a reply to message #399807] Fri, 24 April 2009 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't with PIVOT clause.

Regards
Michel
Re: Oracle 11g Pivot for Subquery [message #399810 is a reply to message #399407] Fri, 24 April 2009 03:36 Go to previous message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Thanks for the information Smile
Previous Topic: extract and display data in a pattern
Next Topic: Converting inline views to temp tables
Goto Forum:
  


Current Time: Sun Dec 04 14:54:18 CST 2016

Total time taken to generate the page: 0.05604 seconds