Home » SQL & PL/SQL » SQL & PL/SQL » PIVOT QUERY NOT WORKING PLEASE HELP
PIVOT QUERY NOT WORKING PLEASE HELP [message #284276] Thu, 29 November 2007 05:09 Go to next message
kumarvk
Messages: 214
Registered: July 2004
Senior Member
SELECT
SUBSTR(A.INV_NO,1,3) "TYPE",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'JAN',SUM(B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT),0)) "JAN"
FROM MSS_INV_HDR A,MSS_INV_LINES B,MSS_RO_HDR C
WHERE
c.ROST_CODE like '%SER%'
AND a.sts IN('I','S','P')
AND A.inv_dtime BETWEEN TO_DATE('01-JAN-07 00:00:00', 'dd-mon-yy hh24:mi:ss')
AND TO_DATE('30-NOV-07 23:59:59', 'dd-mon-yy hh24:mi:ss')
AND A.ID=B.INHD_ID
AND a.ROHD_ID=c.ID
AND C.MAKE_CODE IN ('T7','O7')
GROUP BY TO_CHAR(A.inv_dtime,'MON'),SUBSTR(A.INV_NO,1,3)
ORDER BY TO_DATE(TO_CHAR(A.INV_DTIME,'MON'),'MON');

I have the above sql but when i run it gives me error message
ORA-00937 not a single-group group function

I want the result like this

TYPE JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
SAC
SAD
RAD
SAI
WAR
Re: PIVOT QUERY NOT WORKING PLEASE HELP [message #284285 is a reply to message #284276] Thu, 29 November 2007 05:31 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
could you just follow the guidelines of "HOW TO FORMAT YOUR CODE".

Quote:

sum(decode(TO_CHAR(a.INV_dtime,'MON'),'JAN',SUM(B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT),0)) "JAN"
i think this might be the reason for your error.


regards,

[Updated on: Thu, 29 November 2007 05:35]

Report message to a moderator

Re: PIVOT QUERY NOT WORKING PLEASE HELP [message #284309 is a reply to message #284276] Thu, 29 November 2007 06:13 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: ORA-00054: resource busy and acquire with NOWAIT specified(Error)
Next Topic: Simple SQL Question
Goto Forum:
  


Current Time: Mon Dec 02 08:45:58 CST 2024