This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Jan 8, 2013 11:38 AM by 858164 RSS

sql error ORA-00900: invalid SQL statement

858164 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Use:
    SELECT  NUMTODSINTERVAL(SUM(lg_end_time - lg_start_time))
      FROM  test_1
    /
    SY.
  • 4. Re: sql error ORA-00900: invalid SQL statement
    sb92075 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points