8 Replies Latest reply: Feb 18, 2014 6:14 AM by JamesGordon RSS

    Query Error

    2611484

      Hello! Please help me I'm having a trouble in my query. Thank you

       

       

      I want to display like this:

       

      " Data, Date, No. of POS, No. of user "

       

      I'm always getting this error:

       

      ORA-00904: "G"."CREATE_TIME": invalid identifier

      00904. 00000 -  "%s: invalid identifier"

       

       

      *open_date is an alias of create_time column which is create_time is existing

       

      SELECT 'Data'

      ||','||To_char(d.dtime_day, 'MM/dd/yyyy')

      ||','||NVL(g.ticket_customer_user_id, 0)

      ||','||NVL(u.ticket_customer_user_id, 0)

      FROM owner_dwh.dc_date d

      left join (

                  SELECT trunc(t.create_time) as open_date

                        ,Count(CASE WHEN Upper(t.ticket_customer_user_id) = Upper('POS-generic') THEN 1 ELSE NULL END) as POS

                        ,Count(CASE WHEN Upper(t.ticket_customer_user_id) = Upper('hcphuser') THEN 1 ELSE NULL END) as Office

                  FROM app_account.otrs_ticket t

                  WHERE trunc(t.create_time) BETWEEN SYSDATE - 119 AND SYSDATE - 1

                  AND t.queue_id NOT IN ( 63, 61, 69, 59, 58, 60, 56, 64, 65, 23, 67, 68, 57 )

                  GROUP BY trunc(t.create_time) desc

      ) g ON d.dtime_day = g.create_time

      left join (

                  SELECT trunc(t.close_time) as close_date

                        ,Count(CASE WHEN Upper(t.ticket_customer_user_id) = Upper('POS-generic') THEN 1 ELSE NULL END) as POS

                        ,Count(CASE WHEN Upper(t.ticket_customer_user_id) = Upper('hcphuser') THEN 1 ELSE NULL END) as Office

      FROM app_account.otrs_ticket t

      WHERE  Trunc(t.close_time, 'DAY') BETWEEN SYSDATE - 119 AND SYSDATE - 1

      AND t.queue_id NOT IN ( 63, 61, 69, 59, 58, 60, 56, 64, 65, 23, 67, 68, 57 )

      GROUP BY Trunc(t.close_time)

      ) u ON d.dtime_day = u.close_time

      WHERE d.DTIME_DAY BETWEEN SYSDATE -119 AND TRUNC(SYSDATE) -1

      AND TRUNC(d.DTIME_DAY)= d.DTIME_DAY

      ORDER BY D.DTIME_DAY;