PIVOT QUERY (ROW to COLUMN) ERROR

hi All,

My sql is:

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",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'FEB',SUM(B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT),0)) "FEB",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'MAR',SUM(B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT),0)) "MAR"
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);

When I run getting the error ORA-00937 not a single-group group function

I need the output like this

Type Jan Feb Mar Apr Dec
SAC
SAD
SAI
RAD

Can anyone help me to solve this query?

because following columns are not in group by ....
---------------------------------------------------
B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT

would you paste the codes of related table structures and some data insert sql?

then i think i can help you

you can find how to use the "group by" in oracle function help.
try to solve it by yourself.

if you cannot find the answer, then paste the codes including table structure and some data insert sql. I think i or other people will help you.

Hi,

As per your request I have zipped three sqls with table structure and sample data. I have given for two days only namely Jan 31 and Feb 01.

I hope this will help to solve my problem. If you need anything let me know. Please kindly help thanks.

sorry, i have say wrong for my first reply.
If you use "select colA, colB, colC from Tb group by ..."
then the colA, colB, colC must all in group by's column,
or colA, colB, colC must use cluster functions, such as sum,max.

by the way, the code in your attachment have 2 mistakes:
1.the table name become INV_HDR,INV_LINES,RO_HDR,
not like your tip's(MSS_INV_HDR,MSS_INV_LINES,MSS_RO_HDR)
2.the table RO_HDR doesn't have column which called ROST_CODE,
so i ignore the column in conditions.

anyway, your sql is not bad, only to experice more, to study oracle function deeply.

the following sql is the answer.

Code:

---

SELECT SUBSTR(A.INV_NO,1,3) "TYPE",
      sum(decode(TO_CHAR(a.INV_dtime,'MON'),'JAN',B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT,0)) "JAN",
      sum(decode(TO_CHAR(a.INV_dtime,'MON'),'FEB',B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT,0)) "FEB"
FROM INV_HDR A,INV_LINES B,RO_HDR C
WHERE --c.ROST_CODE like '%SER%'
  --AND
  a.sts IN('I','S','P')
  AND A.inv_dtime BETWEEN TO_DATE('01-01-07 00:00:00', 'dd-mon-yy hh24:mi:ss')
  AND TO_DATE('30-11-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)

---

you needn't use sum 2 times, only one time enough

Sorry, I found some repeat types in my result.
So you must used sum 2 times, the second time for outside

select t.type, sum(t.jan), sum(t.feb) from
(
SELECT SUBSTR(A.INV_NO,1,3) "TYPE",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'JAN',B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT,0)) "JAN",
sum(decode(TO_CHAR(a.INV_dtime,'MON'),'FEB',B.LBR_AMT+B.MTL_AMT+B.PARTS_AMT,0)) "FEB"
FROM INV_HDR A,INV_LINES B,RO_HDR C
WHERE --c.ROST_CODE like '%SER%'
--AND
a.sts IN('I','S','P')
AND A.inv_dtime BETWEEN TO_DATE('01-01-07 00:00:00', 'dd-mon-yy hh24:mi:ss')
AND TO_DATE('30-11-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)
) t
group by t.type;

thank you so much my dear fren, i was about to create a view and do it but you helped thank you very much. take care bye