1 2 Previous Next 22 Replies Latest reply: May 17, 2013 6:56 AM by EdStevens RSS

    Date range calculation

    user11237452
      Hi everyone,

      I have a SQL as below to calculate the old data in history table based on date range, it getting error
      PL/SQL: ORA-00904: "APR": invalid identifier
      INSERT INTO MSG_LOG (SELECT * FROM MSG WHERE (SYSDATE - 16-APR-13-20) >10);
      DELETE FROM MSG WHERE (SYSDATE - 16-APR-13-20) >10; 
      The date value 16-APR-13 actually stores in a variable ln_date with datatype DATE, why it couldn't perform calculation with SYSDATE?
        • 1. Re: Date range calculation
          jeneesh
          When you pass a date value, you need to pass it as a string and should use TO_DATE to conevrt it to date

          to_date('16-APR-2013','dd-MON-yyyy')
          When you pass as
          SYSDATE - 16-APR-13-20
          It will be read as =>

          sysdate minus 16 minus APR minus 13 miinus 20.

          And ORACLE dont know, What APR is..

          Edited by: jeneesh on May 16, 2013 9:46 AM

          Your code will finally be,
          INSERT INTO MSG_LOG 
          (
           SELECT * 
           FROM MSG 
           WHERE (SYSDATE - to_date('16-APR-2013','dd-MON-yyyy') - 20) >10 
          );
          
          DELETE FROM MSG 
          WHERE (SYSDATE - to_date('16-APR-2013','dd-MON-yyyy')-20) >10; 
          • 2. Re: Date range calculation
            JamunaJawahar
            +16-APR-13+ should be to_date('16-APR-2013','DD-MON-YYYY')

            Edited by: Jamuna Jawahar on May 16, 2013 9:47 AM
            • 3. Re: Date range calculation
              Manik
              I am sure you are getting that date dynamically from somewhere.. But the approach should be like this,
              INSERT INTO MSG_LOG
                 (SELECT *
                    FROM MSG
                   WHERE (SYSDATE - (TO_DATE ('16-APR-13', 'DD-MON-YY')) - 20) > 10);
              
              
              DELETE FROM MSG
                    WHERE (SYSDATE - (TO_DATE ('16-APR-13', 'DD-MON-YY')) - 20) > 10;
              
              COMMIT;
              Cheers,
              Manik.
              • 4. Re: Date range calculation
                Etbin
                Maybe:
                insert into msg_log (select * from msg where (sysdate - date '2013-04-16' - 20) > 10);
                delete from msg where (sysdate - date '2013-04-16' - 20) > 10; 
                Reards

                Etbin

                Oops! should have checked before posting
                • 5. Re: Date range calculation
                  user11237452
                  It works if i manually run the script by manually passing the date with to_date. However, when I corporates with my code variable, the error still the same.

                  DECLARE

                  ln_date DATE;
                  sql_stmt VARCHAR2 (300);
                  BEGIN

                  sql_stmt :=
                  'BEGIN INSERT INTO '
                  ||hist_tbl
                  || ' (SELECT * FROM '
                  || pi_table_name
                  || ' WHERE (SYSDATE - '
                  || TO_DATE(ln_date,'dd/mm/yyyy')
                  || '-'
                  ||20
                  || ') >'
                  ||10
                  || '); DELETE FROM '
                  || pi_table_name
                  || ' WHERE (SYSDATE - '
                  || TO_DATE(ln_date,'dd/mm/yyyy')
                  || '-'
                  ||20
                  || ') >'
                  ||10
                  || '; end;';

                  EXECUTE IMMEDIATE sql_stmt;
                  END;
                  • 6. Re: Date range calculation
                    jeneesh
                    You should use BIND Variables..Like
                    --Not tested
                    
                    sql_stmt :=
                    'BEGIN INSERT INTO '
                    ||hist_tbl
                    || ' (SELECT * FROM '
                    || pi_table_name
                    || ' WHERE (SYSDATE - :dt1 -20 ) > 10 );'
                    || ' DELETE FROM '
                    || pi_table_name
                    || ' WHERE (SYSDATE - :dt2 - 20 ) > 10 ;'
                    || ' end;';
                    
                    EXECUTE IMMEDIATE sql_stmt using ln_date,ln_date;
                    First of all, do you actually need dynamic SQL?

                    Edited by: jeneesh on May 16, 2013 10:12 AM
                    • 7. Re: Date range calculation
                      user11237452
                      I need to run the dynamoc SQL in a loop based on some parameter in the loop cycle? What is :dt1 and :dt2?
                      • 8. Re: Date range calculation
                        Shahzad.H.Magsi
                        jeneesh wrote:
                        You should use BIND Variables..Like
                        --Not tested
                        
                        sql_stmt :=
                        'BEGIN INSERT INTO '
                        ||hist_tbl
                        || ' (SELECT * FROM '
                        || pi_table_name
                        || ' WHERE (SYSDATE - :dt1 -20 ) > 10 );'
                        || ' DELETE FROM '
                        || pi_table_name
                        || ' WHERE (SYSDATE - :dt2 - 20 ) > 10 ;'
                        || ' end;';
                        
                        EXECUTE IMMEDIATE sql_stmt using ln_date,ln_date;
                        First of all, do you actually need dynamic SQL?

                        Edited by: jeneesh on May 16, 2013 10:12 AM
                        but Jeneesh kindly note that unless we are using bind variables in report as a formula column then only we don't need formatting of the bind variable as we will be returning it in a format specified in that CF

                        else as a plsql block
                        || ' WHERE (SYSDATE - :dt1 -20 ) > 10 );'
                        || ' WHERE (SYSDATE - :dt2 - 20 ) > 10 ;'
                        should be written as
                        ||'WHERE((SYSDATE - to_date(&dt1,'DD/MM/RRRR')) - 20) > 10;
                        ||'WHERE((SYSDATE - to_date(&dt1,'DD/MM/RRRR')) - 20) > 10;
                        Regards
                        • 9. Re: Date range calculation
                          jeneesh
                          Shahzad.H.Magsi wrote:
                          but Jeneesh kindly note that unless we are using bind variables in report as a formula column then only we don't need formatting of the bind variable as we will be returning it in a format specified in that CF
                          I feel, you assumed the OP is using ORACLE reports builder..
                          >
                          else as a plsql block
                          || ' WHERE (SYSDATE - :dt1 -20 ) > 10 );'
                          || ' WHERE (SYSDATE - :dt2 - 20 ) > 10 ;'
                          should be written as
                          ||'WHERE((SYSDATE - to_date(&dt1,'DD/MM/RRRR')) - 20) > 10;
                          ||'WHERE((SYSDATE - to_date(&dt1,'DD/MM/RRRR')) - 20) > 10;
                          Regards
                          Did not work with reports for a long time..

                          Any how you could take the value int a variable first and then bind it
                          ln_date := to_date(&dt1,......);
                          Then bind the above variable in the dynamic SQL (If dynamic is required)
                          • 10. Re: Date range calculation
                            user11237452
                            not really understand. How should I create the variable to bind the ln_date and assign it properly in the dynamic SQL?
                            • 11. Re: Date range calculation
                              user11237452
                              Below is my dynamic SQL. I assigned the date the variable ln_date which datatype is DATE. I try with and without TO_DATE the result still the same. Oracle treat as SYSDATE - 10-APR-13-20, so 'APR' is invalid.
                              sql_stmt :=
                                          'BEGIN INSERT INTO '
                                       || 'MSG_LOG'
                                       || ' (SELECT * FROM '
                                       || 'MSG'
                                       || ' WHERE (SYSDATE - '
                                       || (TO_DATE(ln_date,'dd/mm/yyyy'))
                                       || '-' 
                                       || 20
                                       || ') >'
                                       ||10    
                                       || '); DELETE FROM '
                                       || 'MSG'
                                       || ' WHERE (SYSDATE - '
                                       || (TO_DATE(ln_date,'dd/mm/yyyy'))
                                       || '-' 
                                       || 20
                                       || ') >' 
                                       ||10
                                       || '; end;';
                              • 12. Re: Date range calculation
                                EdStevens
                                user11237452 wrote:
                                Below is my dynamic SQL. I assigned the date the variable ln_date which datatype is DATE. I try with and without TO_DATE the result still the same. Oracle treat as SYSDATE - 10-APR-13-20, so 'APR' is invalid.
                                sql_stmt :=
                                'BEGIN INSERT INTO '
                                || 'MSG_LOG'
                                || ' (SELECT * FROM '
                                || 'MSG'
                                || ' WHERE (SYSDATE - '
                                || (TO_DATE(ln_date,'dd/mm/yyyy'))
                                || '-' 
                                || 20
                                || ') >'
                                ||10    
                                || '); DELETE FROM '
                                || 'MSG'
                                || ' WHERE (SYSDATE - '
                                || (TO_DATE(ln_date,'dd/mm/yyyy'))
                                || '-' 
                                || 20
                                || ') >' 
                                ||10
                                || '; end;';
                                so if LN_DATE is already a DATE, why are you passing it to TO_DATE?
                                see http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions203.htm#i1003589

                                Purpose

                                TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE data type.
                                • 13. Re: Date range calculation
                                  user11237452
                                  No, If I do not pass it to TO_DATE, it still getting 'APR' is invalid. What should be the correct parsing?
                                  sql_stmt :=
                                              'BEGIN INSERT INTO '
                                           || 'MSG_LOG'
                                           || ' (SELECT * FROM '
                                           || 'MSG'
                                           || ' WHERE (SYSDATE - '
                                           || ln_date
                                           || '-' 
                                           || 20
                                           || ') >'
                                           ||10    
                                           || '); DELETE FROM '
                                           || 'MSG'
                                           || ' WHERE (SYSDATE - '
                                           || ln_date
                                           || '-' 
                                           || 20
                                           || ') >' 
                                           ||10
                                           || '; end;';
                                  • 14. Re: Date range calculation
                                    Etbin
                                    Maybe
                                    sql_stmt := 'BEGIN INSERT INTO MSG_LOG (SELECT * FROM MSG WHERE (SYSDATE - :the_date - 20) > 10); ' ||
                                                '      DELETE FROM MSG WHERE (SYSDATE - :the_date - 20) > 10; ' ||
                                                'END; ';
                                                
                                    ln_date := date '2013-04-16';
                                    
                                    execute immediate sql_stmt using ln_date,ln_date;
                                    Regards

                                    Etbin
                                    1 2 Previous Next