DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: PIVOT QUERY (ROW to COLUMN) ERROR

  1. #1
    Join Date
    Jun 2006
    Posts
    11

    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?

  2. #2
    Join Date
    Nov 2007
    Location
    China
    Posts
    22
    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
    Last edited by anyoneokay; 11-30-2007 at 01:48 AM.

  3. #3
    Join Date
    Nov 2007
    Location
    China
    Posts
    22
    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.

  4. #4
    Join Date
    Jun 2006
    Posts
    11
    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.
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2007
    Location
    China
    Posts
    22
    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)
    Last edited by anyoneokay; 11-30-2007 at 03:27 AM.

  6. #6
    Join Date
    Nov 2007
    Location
    China
    Posts
    22
    you needn't use sum 2 times, only one time enough

  7. #7
    Join Date
    Nov 2007
    Location
    China
    Posts
    22
    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;

  8. #8
    Join Date
    Jun 2006
    Posts
    11
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width