1 2 Previous Next 19 Replies Latest reply: Jan 8, 2013 1:38 PM by 858164 RSS

    sql error ORA-00900: invalid SQL statement

    858164
      Hi All

      I am new to sql and i try to solve this issue i have here.

      where i run this query (1) i get this message : ORA-00900: invalid SQL statement

      Query : 1


      WITH t1
      AS (SELECT CID ,
      TYPE,
      TO_CHAR (
      TO_DATE ('00:00:00', 'HH24:MI:SS')
      + (lg_end_time - lg_start_time),
      'HH24:MI:SS') call_time,
      ROW_NUMBER ()
      OVER (PARTITION BY CID ORDER BY CID NULLS FIRST)
      AS call_id1
      FROM test_1
      )
      SELECT SUM (call_time)
      FROM t1;
      output:
      i get error: ORA-01722: invalid number




      table structure:

      select * form test_1;

      CID            TYPE                      LG_END_TIME               LG_START_TIME
      1508643     Dispatching     2012/12/03 14:05     2012/12/03 14:02
      1508643     Treatment     2012/12/03 14:00      2012/12/03 14:00
      1508643     Initiation     2012/12/03 14:00     2012/12/03 14:00
      1508662     Dispatching     2012/12/03 14:18     2012/12/03 14:16
      1508662     Initiation     2012/12/03 14:01     2012/12/03 14:01
      1508662     Treatment     2012/12/03 14:02      2012/12/03 14:01
      1508643     Dispatching     2012/12/03 14:02     2012/12/03 14:00
      1508662     Dispatching     2012/12/03 14:16     2012/12/03 14:02

      thanks for all your help
        • 1. Re: sql error ORA-00900: invalid SQL statement
          ranit B
          What is this ?
          TO_CHAR(
               TO_DATE ('00:00:00', 'HH24:MI:SS') + (lg_end_time - lg_start_time) ,'HH24:MI:SS'
          ) call_time
          Here,
          (lg_end_time - lg_start_time) : gives a DATE
          and
          TO_DATE ('00:00:00', 'HH24:MI:SS') : gives time

          Adding both and then trying to format it... is giving the error i guess

          Please check that once...
          Ranit B.

          Edited by: ranit B on Dec 21, 2012 11:00 PM
          • 2. Re: sql error ORA-00900: invalid SQL statement
            Frank Kulash
            Hi,
            855161 wrote:
            Hi All

            I am new to sql and i try to solve this issue i have here.

            where i run this query (1) i get this message : ORA-00900: invalid SQL statement

            Query : 1


            WITH t1
            AS (SELECT CID ,
            TYPE,
            TO_CHAR (
            TO_DATE ('00:00:00', 'HH24:MI:SS')
            + (lg_end_time - lg_start_time),
            'HH24:MI:SS') call_time,
            ROW_NUMBER ()
            OVER (PARTITION BY CID ORDER BY CID NULLS FIRST)
            AS call_id1
            FROM test_1
            )
            SELECT SUM (call_time)
            FROM t1;
            output:
            i get error: ORA-01722: invalid number
            call_time is a VARCHAR2, such as '12:27:15'. You can't add VARCHAR2s (that is, the argument to SUM must be a NUMBER).
            I think you wnat something like this:
            SELECT      24 * SUM ( lg_end_time
                              - lg_start_time
                        )          AS total_call_hours
            FROM     test_1
            ;
            or, if you want formatted output
            SELECT      NUMTODSINTERVAL ( SUM ( lg_end_time
                                          - lg_start_time
                                  )
                            , 'DAY'
                              )     AS total_call_time
            FROM     test_1
            ;
            table structure:

            select * form test_1;

            CID            TYPE                      LG_END_TIME               LG_START_TIME
            1508643     Dispatching     2012/12/03 14:05     2012/12/03 14:02
            1508643     Treatment     2012/12/03 14:00      2012/12/03 14:00
            ...
            Always post CREATE TABLE and INSERT statements for the sameple data, and the results you want from that data.
            See the forum FAQ {message:id=9360002}
            • 3. Re: sql error ORA-00900: invalid SQL statement
              Solomon Yakobson
              Use:
              SELECT  NUMTODSINTERVAL(SUM(lg_end_time - lg_start_time))
                FROM  test_1
              /
              SY.
              • 4. Re: sql error ORA-00900: invalid SQL statement
                sb92075
                post results from the following SQL
                WITH t1 
                     AS (SELECT cid, 
                                type, 
                                To_char (To_date ('00:00:00', 'HH24:MI:SS') + ( 
                                         lg_end_time - lg_start_time ), 
                                'HH24:MI:SS')                 call_time, 
                                Row_number () 
                                  OVER ( 
                                    partition BY cid 
                                    ORDER BY cid nulls first) AS call_id1 
                         FROM   test_1) 
                SELECT call_time 
                FROM   t1; 
                Handle:     855161
                Status Level:     Newbie
                Registered:     Apr 27, 2011
                Total Posts:     62
                Total Questions:     16 (12 unresolved)

                why so MANY unanswered questions?
                • 5. Re: sql error ORA-00900: invalid SQL statement
                  ReubenC
                  If I'm reading the parentheses correctly, call_time is a char as it is the result of a TO_CHAR. Obviously, converted dates to chars and trying to add them can be problematic. Try it without the to_char first.
                  • 6. Re: sql error ORA-00900: invalid SQL statement
                    858164
                    Hi thanks if you run the subquery the select statment. it gives resutls

                    for example :
                    SELECT CID ,
                                    TYPE,
                                    TO_CHAR (
                                         TO_DATE ('00:00:00', 'HH24:MI:SS')
                                       + (lg_end_time - lg_start_time),
                                       'HH24:MI:SS') call_time,
                                    ROW_NUMBER ()
                                       OVER (PARTITION BY CID ORDER BY CID NULLS FIRST)
                                       AS call_id1
                               FROM test_1;
                    the out put works:

                    table Structure:
                    CREATE TABLE TEST_1
                    (
                      CID            NUMBER                         NOT NULL,
                      TYPE           VARCHAR2(20 BYTE)              NOT NULL,
                      LG_END_TIME    DATE                           NOT NULL,
                      LG_START_TIME  DATE                           NOT NULL
                    )
                    what i am trying to do here is to get : sum by doing group by CID

                    Edited by: 855161 on Dec 21, 2012 10:54 AM

                    Edited by: 855161 on Dec 21, 2012 10:55 AM
                    • 7. Re: sql error ORA-00900: invalid SQL statement
                      Solomon Yakobson
                      855161 wrote:
                      what i am trying to do here is to get : sum by doing group by CID
                      SELECT  CID,
                              NUMTODSINTERVAL(SUM(call_time))
                        FROM  test_1
                        GROUP BY CID
                      /
                      SY.
                      • 8. Re: sql error ORA-00900: invalid SQL statement
                        ranit B
                        855161 wrote:
                        Hi thanks if you run the subquery the select statment. it gives resutls

                        for example :
                        SELECT CID ,
                        TYPE,
                        TO_CHAR (
                        TO_DATE ('00:00:00', 'HH24:MI:SS')
                        + (lg_end_time - lg_start_time),
                        'HH24:MI:SS') call_time,
                        ROW_NUMBER ()
                        OVER (PARTITION BY CID ORDER BY CID NULLS FIRST)
                        AS call_id1
                        FROM test_1;
                        the out put works:

                        table Structure:
                        CREATE TABLE TEST_1
                        (
                        CID            NUMBER                         NOT NULL,
                        TYPE           VARCHAR2(20 BYTE)              NOT NULL,
                        LG_END_TIME    DATE                           NOT NULL,
                        LG_START_TIME  DATE                           NOT NULL
                        )
                        what i am trying to do here is to get : sum by doing group by CID

                        Edited by: 855161 on Dec 21, 2012 10:54 AM

                        Edited by: 855161 on Dec 21, 2012 10:55 AM
                        Do a GROUP BY CID,TYPE and then do the SUM(call_time)... please try.

                        *Please provide the required test tables and the data insert scripts. Help us to help you.*
                        • 9. Re: sql error ORA-00900: invalid SQL statement
                          858164
                          HI
                          Solomon


                          thanks i used this before what i have to do is to have the other columns with the group by that's why i am using
                          sql analytics : over (partition by call_id)

                          with this i can group by multiple columns whereas using simple group by that you indicated i get few columns.


                          if you can help me with the error that will be great.
                          sql error ORA-00900: invalid SQL statement 
                          Edited by: 855161 on Dec 21, 2012 11:12 AM
                          • 10. Re: sql error ORA-00900: invalid SQL statement
                            Solomon Yakobson
                            Oops, I posted wrong piece. Sould be:
                            SELECT  CID,
                                    NUMTODSINTERVAL(SUM(LG_END_TIME - LG_START_TIME),'DAY')
                              FROM  test_1
                              GROUP BY CID
                            / 
                            SY.
                            • 11. Re: sql error ORA-00900: invalid SQL statement
                              858164
                              Hi All


                              thanks for helping and giving your support. but the at this time question remains unanswered.
                              with t1
                              as (
                              SELECT CID ,
                                              TYPE,
                                              TO_CHAR (
                                                   TO_DATE ('00:00:00', 'HH24:MI:SS')
                                                 + (lg_end_time - lg_start_time),
                                                 'HH24:MI:SS') call_time,
                                              ROW_NUMBER ()
                                                 OVER (PARTITION BY CID ORDER BY CID NULLS FIRST)
                                                 AS call_id1
                                         FROM test_1
                                         )
                                         select * from t1
                              Output:
                              CID             TYPE           CALL_TIME     CALL_ID1
                              1508643     Dispatching     0:02:06     1
                              1508643     Initiation     0:00:00     2
                              1508643     Treatment     0:00:39     3
                              1508643     Dispatching     0:02:50     4
                              1508662     Treatment     0:01:03     1
                              1508662     Initiation     0:00:00     2
                              1508662     Dispatching     0:13:17     3
                              1508662     Dispatching     0:02:43     4
                              Desired results would be :

                              group by CID and total time (summed) by each CID
                              and it will also look like:
                              CID             TYPE        CALL_TIME     CALL_ID1   total_time
                              1508643     Dispatching     0:02:06     1
                              1508643     Initiation     0:00:00     2
                              1508643     Treatment     0:00:39     3
                              1508643     Dispatching     0:02:50     4             
                                                                                              0:05:35  
                              1508662     Treatment     0:01:03     1 
                              1508662     Initiation     0:00:00     2
                              1508662     Dispatching     0:13:17     3
                              1508662     Dispatching     0:02:43     4       
                                                                                              0:17:03
                              Create table :
                              
                              CREATE TABLE TEST_1
                              ( CID            NUMBER                         NOT NULL,
                                TYPE           VARCHAR2(20 BYTE)              NOT NULL,
                                LG_END_TIME    DATE                           NOT NULL,
                                LG_START_TIME  DATE                           NOT NULL
                              )
                              
                              Insert statement:
                                  insert  into   test_1 VALUES (1508643,Dispatching,03-DEC-12,03-DEC-12);
                                  insert  into   test_1 VALUES (1508643,Treatment,03-DEC-12,03-DEC-12);
                                  insert  into  test_1 VALUES (1508643,Initiation,03-DEC-12,03-DEC-12);
                                  insert  into   test_1 VALUES (1508662,Dispatching,03-DEC-12,03-DEC-12);
                                  insert  into   test_1 VALUES (1508662,Initiation,03-DEC-12,03-DEC-12);
                                  insert  into   test_1 VALUES (1508662,Treatment,03-DEC-12,03-DEC-12);
                                  insert  into   test_1 VALUES (1508643,Dispatching,03-DEC-12,03-DEC-12);
                                  insert  into   test_1 VALUES (1508662,Dispatching,03-DEC-12,03-DEC-12);
                              Edited by: 855161 on Jan 7, 2013 8:37 AM

                              Edited by: 855161 on Jan 7, 2013 9:15 AM

                              Edited by: 855161 on Jan 7, 2013 1:00 PM
                              • 12. Re: sql error ORA-00900: invalid SQL statement
                                Frank Kulash
                                Hi,

                                It sounds like you want this:
                                WITH       t1     AS 
                                (
                                     SELECT    cid
                                     ,       type
                                     ,       lg_end_time - lg_start_time     AS call_time_in_days
                                     ,       ROW_NUMBER () OVER ( PARTITION BY  cid 
                                                                  ORDER BY      NULL
                                                         )                AS call_id1
                                     FROM      test_1
                                )
                                SELECT   cid
                                ,      type
                                ,      TO_CHAR ( TRUNC (SYSDATE) + call_time_in_days
                                            , 'HH24:MI:SS'
                                           )          AS call_time
                                ,       call_id1
                                ,       CASE
                                           WHEN  GROUPING (call_time_in_days)
                                           THEN  TO_CHAR ( TRUNC (SYSDATE) + SUM (call_time_in_days)
                                                             , 'HH24:MI:SS'
                                                   )
                                       END     AS total_time
                                FROM        t1
                                GROUP BY  GROUPING SETS ( (cid, type, call_time_in_days, call_id1)
                                                          , (cid)
                                               )
                                ORDER BY  cid
                                ,            call_id1
                                ;
                                Without the sample data (CREATE TABLE and INSERT statements) that produce the given results, I can't be sure.
                                • 13. Re: sql error ORA-00900: invalid SQL statement
                                  Warren Tolentino
                                  maybe this example might help.
                                  SQL> select lpad(hours,2,'0')||':'||lpad(minutes,2,'0')||':'||lpad(seconds,2,'0') time
                                    2    from (
                                    3  select sum(to_number(to_char(dt,'hh24'))) + trunc((sum(to_number(to_char(dt,'mi'))) + (trunc(sum(to_number(to_char(dt,'ss')))/60)))/24) hours,
                                    4         sum(to_number(to_char(dt,'mi'))) + (trunc(sum(to_number(to_char(dt,'ss')))/60)) minutes,
                                    5         (sum(to_number(to_char(dt,'ss'))) -  ((trunc(sum(to_number(to_char(dt,'ss')))/60)) * 60)) seconds
                                    6    from ( select to_date('00:02:06','hh24:mi:ss') dt from dual union all
                                    7           select to_date('00:00:00','hh24:mi:ss') dt from dual union all
                                    8           select to_date('00:00:39','hh24:mi:ss') dt from dual union all
                                    9           select to_date('00:02:50','hh24:mi:ss') dt from dual ));
                                  
                                  TIME
                                  --------
                                  00:05:35
                                  
                                  SQL> 
                                  since we cannot use the sum function to add altogether the time we will have to find some way to segregrate the seconds, minutes, and time. first to we will use the smallest unit of the time and that is the seconds. then use that to add them to minutes, any seconds that goes beyond 60 will count as 1 and add them minutes. if it beyond 120 will count as 2 and so on. same thing to applies to minutes, any minutes that goes beyond 60 will count as 1 and add them to the hours.
                                  • 14. Re: sql error ORA-00900: invalid SQL statement
                                    858164
                                    Hi


                                    here is the table structure and the table data ;

                                    CREATE TABLE TEST_1
                                    ( CID            NUMBER                         NOT NULL,
                                      TYPE           VARCHAR2(20 BYTE)              NOT NULL,
                                      LG_END_TIME    DATE                           NOT NULL,
                                      LG_START_TIME  DATE                           NOT NULL
                                    )
                                     SET DEFINE OFF;
                                    Insert into TEST_1
                                       (CID, TYPE, LG_END_TIME, LG_START_TIME)
                                     Values
                                       (1508643, 'Dispatching', TO_DATE('12/03/2012 14:05:36', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/03/2012 14:02:46', 'MM/DD/YYYY HH24:MI:SS'));
                                    Insert into TEST_1
                                       (CID, TYPE, LG_END_TIME, LG_START_TIME)
                                     Values
                                       (1508643, 'Treatment', TO_DATE('12/03/2012 14:00:40', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/03/2012 14:00:01', 'MM/DD/YYYY HH24:MI:SS'));
                                    Insert into TEST_1
                                       (CID, TYPE, LG_END_TIME, LG_START_TIME)
                                     Values
                                       (1508643, 'Initiation', TO_DATE('12/03/2012 14:00:01', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/03/2012 14:00:01', 'MM/DD/YYYY HH24:MI:SS'));
                                    Insert into TEST_1
                                       (CID, TYPE, LG_END_TIME, LG_START_TIME)
                                     Values
                                       (1508662, 'Dispatching', TO_DATE('12/03/2012 14:18:44', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/03/2012 14:16:01', 'MM/DD/YYYY HH24:MI:SS'));
                                    Insert into TEST_1
                                       (CID, TYPE, LG_END_TIME, LG_START_TIME)
                                     Values
                                       (1508662, 'Initiation', TO_DATE('12/03/2012 14:01:41', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/03/2012 14:01:41', 'MM/DD/YYYY HH24:MI:SS'));
                                    Insert into TEST_1
                                       (CID, TYPE, LG_END_TIME, LG_START_TIME)
                                     Values
                                       (1508662, 'Treatment', TO_DATE('12/03/2012 14:02:44', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/03/2012 14:01:41', 'MM/DD/YYYY HH24:MI:SS'));
                                    Insert into TEST_1
                                       (CID, TYPE, LG_END_TIME, LG_START_TIME)
                                     Values
                                       (1508643, 'Dispatching', TO_DATE('12/03/2012 14:02:46', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/03/2012 14:00:40', 'MM/DD/YYYY HH24:MI:SS'));
                                    Insert into TEST_1
                                       (CID, TYPE, LG_END_TIME, LG_START_TIME)
                                     Values
                                       (1508662, 'Dispatching', TO_DATE('12/03/2012 14:16:01', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/03/2012 14:02:44', 'MM/DD/YYYY HH24:MI:SS'));
                                    Edited by: 855161 on Jan 7, 2013 12:59 PM

                                    Edited by: 855161 on Jan 7, 2013 12:59 PM
                                    1 2 Previous Next