Home » SQL & PL/SQL » SQL & PL/SQL » pivot query (11g)
pivot query [message #578520] Thu, 28 February 2013 23:50 Go to next message
annu-agi
Messages: 195
Registered: July 2005
Location: Karachi
Senior Member

hello dear all gurus and experts

I need your help in a query where i am stuck
SELECT *
FROM   (
SELECT ERPCAANO,PAY_MONTH,PAY_YEAR,BANK_BRANCH_CODE,ACC_NUMBER,ACC_CODe,RG_PAY,CR_PAY,DR_PAY
        FROM   for_pivot_data where pay_month=2 and erpcaano='OF1483')
PIVOT  (SUM(rg_pay)AS RGR_PAY, SUM(CR_PAY)AS CR_PAY, SUM(CR_PAY)AS DR_PAY  FOR (acc_code) IN ('801010'));

result is like this
ERPCAANO PAY_MONTH              PAY_YEAR               BANK_BRANCH_CODE       ACC_NUMBER           DR_PAY                 '801010'_RGR_PAY       '801010'_CR_PAY        '801010'_DR_PAY        
-------- ---------------------- ---------------------- ---------------------- -------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
OF1483   2                      2013                   18013                  0104-0100204431                             60950                                                                

1 rows selected



i get my desired result but now i would like to change in query and make it dynamic. When i change
SELECT *
FROM   (
SELECT ERPCAANO,PAY_MONTH,PAY_YEAR,BANK_BRANCH_CODE,ACC_NUMBER,ACC_CODe,RG_PAY,CR_PAY,DR_PAY
        FROM   for_pivot_data where pay_month=2 and erpcaano='OF1483')
PIVOT  (SUM(rg_pay)AS RGR_PAY, SUM(CR_PAY)AS CR_PAY, SUM(CR_PAY)AS DR_PAY  FOR (acc_code) IN 
(select distinct acc_code from for_pivot_data where pay_month=2 and erpcaano='OF1483'));

it gives me error
Error at Command Line:6 Column:1
Error report:
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:



well, i cant understand why it happens, when i give fixed values it works but when i get values from query it stucks. Any idea where i am doing mistake and something i am missing in syntex or command. Please help me out of this


regards and thanks in advance

Anwer Ali


Re: pivot query [message #578525 is a reply to message #578520] Fri, 01 March 2013 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table 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.

Regards
Michel
Re: pivot query [message #579995 is a reply to message #578520] Mon, 18 March 2013 15:08 Go to previous message
Barbara Boehmer
Messages: 7997
Registered: November 2002
Location: California, USA
Senior Member
The Oracle 11g built-in pivot only supports literals, not selects for the in-list. There are various other methods. One of my favorites is a pivot function that I got from the following website that explains and demonstrates it:

http://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

Under "Resources" on the above page, click on AntonsPivoting.zip, download it and run pivotfun.sql. Then you can run dynamic pivot queries.

Previous Topic: Record Types
Next Topic: repeat_interval in oracle Schedule
Goto Forum:
  


Current Time: Thu Oct 30 08:10:02 CDT 2014

Total time taken to generate the page: 0.28650 seconds