1 2 Previous Next 19 Replies Latest reply: Jan 8, 2013 1:38 PM by 858164 Go to original post RSS
      • 15. Re: sql error ORA-00900: invalid SQL statement
        Frank Kulash
        855161 wrote:
        here is the table structure and the table data ;
        ...
        CREATE TABLE PREPAREA.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);
        ...
        None of those INSERT statem,ents work when I try to run them.
        03-DEC-12
        is not a DATE; it's just an error. Use something like
        TO_DATE ( '03-DEC-2012'
             , 'DD-MON-YYYY'
             )
        or a DATE literal
        DATE '2012-12-03'
        instead.
        • 16. Re: sql error ORA-00900: invalid SQL statement
          Warren Tolentino
          855161 wrote:
          Hi


          here is the table structure and the table data ;

          CREATE TABLE PREPAREA.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); 
          i attempt to use your posting the insert statement and looks like you have some missing entries such as making the dates to enclosed in to_date. also you did not include the time on the date. without a correct data to work on i guess you will have to try to run this example to see if this works.
          select decode(call_id1,99,null,cid)        cid,
                 decode(call_id1,99,null,type)       type, 
                 decode(call_id1,99,null,call_time)  call_time, 
                 decode(call_id1,99,null,call_id1)   call_id1, 
                 decode(call_id1,99,null,total_time) total_time
            from (select cid,
                         type,
                         call_time,
                         call_id1,
                         null total_time
                    from (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 ) t1
                  union all
                  select cid,
                         null type,
                         null call_time,
                         99   call_id1
                         lpad(hours,2,'0')||':'||lpad(minutes,2,'0')||':'||lpad(seconds,2,'0') total_time,
                    from (select cid,
                                 sum(to_number(to_char(call_time,'hh24'))) + trunc((sum(to_number(to_char(call_time,'mi'))) + (trunc(sum(to_number(to_char(call_time,'ss')))/60)))/24) hours,
                                 sum(to_number(to_char(call_time,'mi'))) + (trunc(sum(to_number(to_char(call_time,'ss')))/60)) minutes,
                                 (sum(to_number(to_char(call_time,'ss'))) -  ((trunc(sum(to_number(to_char(call_time,'ss')))/60)) * 60)) seconds
                            from (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 ) t1
                          group bu cid) ) 
          order by cid, call_id1
          • 17. Re: sql error ORA-00900: invalid SQL statement
            858164
            Sorry for the incorrect syntax before for the table structure :

            script below is tested :
            --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
               (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 8, 2013 8:42 AM

            Edited by: 855161 on Jan 8, 2013 8:43 AM
            • 18. Re: sql error ORA-00900: invalid SQL statement
              Warren Tolentino
              i think this example might help.
              SQL> select decode(call_id1,99,null,cid)        cid,
                2         decode(call_id1,99,null,type)       type,
                3         decode(call_id1,99,null,to_char(call_time,'hh24:mi:ss'))  call_time,
                4         decode(call_id1,99,null,substr(call_id1,1,5))   call_id1,
                5         total_time
                6    from (select cid,
                7                 type,
                8                 call_time,
                9                 call_id1,
               10                 total_time
               11            from (select cid,
               12                         type,
               13                         call_time,
               14                         call_id1,
               15                         null total_time
               16                    from (SELECT CID ,
               17                                 TYPE,
               18                                 to_date(TO_CHAR (TO_DATE ('00:00:00', 'HH24:MI:SS') +
               19                                        (lg_end_time - lg_start_time),'HH24:MI:SS'),'HH24:MI:SS') call_time,
               20                                 ROW_NUMBER () OVER (PARTITION BY CID ORDER BY CID NULLS FIRST) AS call_id1
               21                            FROM test_1 ) t1
               22                  union all
               23                  select cid,
               24                         null type,
               25                         null call_time,
               26                         99   call_id1,
               27                         lpad(hours,2,'0')||':'||lpad(minutes,2,'0')||':'||lpad(seconds,2,'0') total_time
               28                    from (select cid,
               29                                 sum(to_number(to_char(call_time,'hh24'))) +
               30                                     trunc((sum(to_number(to_char(call_time,'mi'))) +
               31                                     (trunc(sum(to_number(to_char(call_time,'ss')))/60)))/24) hours,
               32                                 sum(to_number(to_char(call_time,'mi'))) +
               33                                    (trunc(sum(to_number(to_char(call_time,'ss')))/60)) minutes,
               34                                 (sum(to_number(to_char(call_time,'ss'))) -
               35                                     ((trunc(sum(to_number(to_char(call_time,'ss')))/60)) * 60)) seconds
               36                            from (SELECT CID ,
               37                                         TYPE,
               38                                         to_date(TO_CHAR (TO_DATE ('00:00:00', 'HH24:MI:SS') +
               39                                                (lg_end_time - lg_start_time),'HH24:MI:SS'),'HH24:MI:SS') call_time,
               40                                         ROW_NUMBER () OVER (PARTITION BY CID ORDER BY CID NULLS FIRST) AS call_id1
               41                                    FROM test_1 ) t1
               42                          group by cid) )
               43          order by cid, call_id1 );
              
              CID                                      TYPE                 CALL_TIME CALL_ID1 TOTAL_TIME
              ---------------------------------------- -------------------- --------- -------- ----------
              1508643                                  Dispatching          00:02:06  1        
              1508643                                  Initiation           00:00:00  2        
              1508643                                  Treatment            00:00:39  3        
              1508643                                  Dispatching          00:02:50  4        
                                                                                               00:05:35
              1508662                                  Treatment            00:01:03  1        
              1508662                                  Initiation           00:00:00  2        
              1508662                                  Dispatching          00:13:17  3        
              1508662                                  Dispatching          00:02:43  4        
                                                                                               00:17:03
              
              10 rows selected
              
              SQL> 
              • 19. Re: sql error ORA-00900: invalid SQL statement
                858164
                great solution


                thanks for the help :)

                Edited by: 855161 on Jan 8, 2013 11:38 AM
                1 2 Previous Next