drop partition using ifelse condition-CodePudding

How should the condition be written correctly so that only one partition drops before 10 am, and after 10 am two partitions?

      if trunc(sysdate, 'hh') < ('10:00:00') then
    
        EXECUTE IMMEDIATE 'ALTER TABLE SOFT_TEST DROP PARTITION FOR (TO_DATE(''' ||
                          TO_CHAR(trunc(sysdate, 'hh'),
                                  'YYYY-MM-DD HH24:MI') ||
                          ''', ''YYYY-MM-DD HH24:MI''))';
      elsif trunc(sysdate, 'hh') >= ('10:00:00') then
        EXECUTE IMMEDIATE 'ALTER TABLE SOFT_TEST DROP PARTITION FOR (TO_DATE(''' ||
                          TO_CHAR(trunc(sysdate - 1/24, 'hh'),
                                  'YYYY-MM-DD HH24:MI') ||
                          ''', ''YYYY-MM-DD HH24:MI''))';
        EXECUTE IMMEDIATE 'ALTER TABLE SOFT_TEST DROP PARTITION FOR (TO_DATE(''' ||
                          TO_CHAR(trunc(sysdate, 'hh'),
                                  'YYYY-MM-DD HH24:MI') ||
                          ''', ''YYYY-MM-DD HH24:MI''))';
   end if;

CodePudding user response：

In your place, I would reverse the conditions of the IF:

IF EXTRACT(HOUR FROM systimestamp) >= 10 THEN
  ...
ELSE
  ...
END IF;

Considering your conditions are alternatives and they cover both states.