1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help with Pivot in Oracle!!!

Discussion in 'General' started by SandraSp73, Aug 14, 2013.

  1. SandraSp73

    SandraSp73 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I need help with my query below and tell me what i am doing wrong? as you can see I'm not an oracle person...

    SELECT TO_CHAR(TO_DATE(TO_CHAR(time_stamp,'000000'),'HH24MISS'),'HH12:MI AM') "TimeStamps", Duration AS "Dur" , Res_ID
    FROM eventdtl
    WHERE To_Date(To_Char(substr(EVENTDTL.date_stamp,2,6)),'yymmdd') >=sysdate-2 and To_Date(To_Char(substr(EVENTDTL.date_stamp,2,6)),'yymmdd') < sysdate
    and res_type = 'T'
    and event_type = '12'
    and res_id IN (1,2,4,5,8,20,21,22,30)
    GROUP BY TO_CHAR(TO_DATE(TO_CHAR(time_stamp,'000000'),'HH24MISS'),'HH12:MI AM'), RES_ID, DURATION

    RESULT:
    TimeStamps Dur RES_ID
    ---------- ---------- ----------
    12:29 AM 0 2
    12:29 AM 0 4
    12:29 AM 0 8
    12:29 AM 0 22
    12:29 AM 0 30
    12:59 AM 0 1
    01:59 AM 0 2
    01:59 AM 0 30
    02:29 AM 0 2
    02:59 AM 0 4

    MY PIVOT QUERY:
    select TimeStamps,1,2,4,5,8,20,21,22,30
    FROM(select TO_CHAR(TO_DATE(TO_CHAR(time_stamp,'000000'),'HH24MISS'),'HH12:MI AM') "TimeStamps", Duration AS "Dur" , Res_ID
    from eventdtl
    where To_Date(To_Char(substr(EVENTDTL.date_stamp,2,6)),'yymmdd') >=sysdate-2 and To_Date(To_Char(substr(EVENTDTL.date_stamp,2,6)),'yymmdd') < sysdate
    and res_type = 'T'
    and event_type = '12'
    and res_id IN (1,2,4,5,8,20,21,22,30)
    )
    PIVOT
    (SUM(Dur) FOR Res_ID IN ([1],[2],[4],[5],[8],[20],[21],[22],[30])
    )
    ERROR:
    Error at Command Line:11 Column:1
    Error report:
    SQL Error: ORA-00933: SQL command not properly ended
    00933. 00000 - "SQL command not properly ended"
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
  3. SandraSp73

    SandraSp73 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I tried without the brackets and ;
    it doesnt appear to get me anymore:(

    also looked the link you provided...i get the same error.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I'm thinking since you have "" around the column alias for Duration you'll need them in the pivot statement:

    Code (SQL):
    SELECT TimeStamps,1,2,4,5,8,20,21,22,30
    FROM(SELECT TO_CHAR(TO_DATE(TO_CHAR(time_stamp,'000000'),'HH24 MISS'),'HH12:MI AM') "TimeStamps", Duration AS "Dur" , Res_ID
    FROM eventdtl
    WHERE To_Date(To_Char(substr(EVENTDTL.date_stamp,2,6)),' yymmdd') >=sysdate-2 AND To_Date(To_Char(substr(EVENTDTL.date_stamp,2,6)),' yymmdd') < sysdate
    AND res_type = 'T'
    AND event_type = '12'
    AND res_id IN (1,2,4,5,8,20,21,22,30)
    )
    PIVOT
    (SUM("Dur") FOR Res_ID IN (1,2,4,5,8,20,21,22,30)
    )