Oracle while loop

Question

This is my current oracle statement:

  WITH table_ AS (
           SELECT DATETIME
                , TOTALTIME1
                , RAWOUTPUT1
                , CASE BITAND(RAWOUTPUT1, POWER(2,0))
                      WHEN 0 THEN 'FALSE'
                      ELSE        'TRUE' 
                  END AS Pumpe1_1 
             FROM pump_box_hist
       )
     , table2_ AS (
          SELECT DATETIME
               , TOTALTIME1
               , RAWOUTPUT1
               , Pumpe1_1
               , LEAD (Pumpe1_1) OVER (ORDER BY datetime)  as next_
               , LAG (Pumpe1_1) OVER (ORDER BY datetime)   as priv_ 
            FROM table_
       )
SELECT DATETIME
     , TOTALTIME1
     , RAWOUTPUT1
     , Pumpe1_1 
  FROM table2_ 
 WHERE (
                Pumpe1_1 = next_
            AND Pumpe1_1 <> priv_
            AND DATETIME > to_date('30.10.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
            AND DATETIME < to_date('02.11.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
       )
     ;

It works fine but it takes nearly 50 seconds to execute. Because the data is sorted by DATETIME the idea is to do a while loop to exit if DATETIME is bigger than the given. Inside this loop I would then use IF for the date smaller than the given. I tried a while but don't get it working.

The code I've tried:

WHILE (SELECT DATETIME FROM pump_box_hist) < to_date('02.11.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
LOOP
    IF DATETIME > to_date('30.10.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS') THEN
        WITH table_ AS (
                    SELECT DATETIME
                         , TOTALTIME1
                         , RAWOUTPUT1
                         , CASE BITAND(RAWOUTPUT1, POWER(2,0)) 
                               WHEN 0 THEN 'FALSE' 
                               ELSE        'TRUE'
                           END AS Pumpe1_1 
                      FROM pump_box_hist
             )
           , table2_ AS (
                   SELECT DATETIME
                        , TOTALTIME1
                        , RAWOUTPUT1
                        , Pumpe1_1
                        , LEAD (Pumpe1_1) OVER (ORDER BY datetime)  as next_
                        , LAG (Pumpe1_1) OVER (ORDER BY datetime)   as priv_ 
                     FROM table_
             )
      SELECT DATETIME
           , TOTALTIME1
           , RAWOUTPUT1
           , Pumpe1_1 
        FROM table2_ 
       WHERE (
                   Pumpe1_1 = next_ 
               AND Pumpe1_1 <> priv_
             )
           ;
    END IF;
END LOOP;

Error: unknown command in line 32 and 33 (Editor's note: these have been lines 11 and 12 in the original formatting containing END IF; and END LOOP;)

So how can I get the LOOP and IF working? Thanks

Answer 1

You're getting the 'unknown command' errors from your client because you're trying to use PL/SQL control statements in plain SQL; while, if, end if and end loop are not valid in SQL.

All you really seem to doing is moving the filters, so they can just go in your first CTE:

  WITH table_ AS (
           SELECT DATETIME
                , TOTALTIME1
                , RAWOUTPUT1
                , CASE BITAND(RAWOUTPUT1, POWER(2,0))
                      WHEN 0 THEN 'FALSE'
                      ELSE        'TRUE' 
                  END AS Pumpe1_1 
             FROM pump_box_hist
            WHERE DATETIME > to_date('30.10.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
              AND DATETIME < to_date('02.11.2014 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
       )
     , table2_ AS (
          SELECT DATETIME
               , TOTALTIME1
               , RAWOUTPUT1
               , Pumpe1_1
               , LEAD (Pumpe1_1) OVER (ORDER BY datetime)  as next_
               , LAG (Pumpe1_1) OVER (ORDER BY datetime)   as priv_ 
            FROM table_
       )
SELECT DATETIME
     , TOTALTIME1
     , RAWOUTPUT1
     , Pumpe1_1 
  FROM table2_ 
 WHERE (
                Pumpe1_1 = next_
            AND Pumpe1_1 <> priv_
       )
     ;

But this now means the first and last rows in table_ won't have an earlier/later rows for lag/lead to find, so priv_/next_ (respectively) will be null for those, which could affect your final results. You can explicitly check for nulls in the final where clause though:

...
  FROM table2_ 
 WHERE (
                (Pumpe1_1 = next_ or next_ is null)
            AND (Pumpe1_1 <> priv_ or priv_ is null)
       )

You could also do this with a single CTE by doing the lead/lag against the raw value rather than the calculated flag, which won't perform very differently but is a little shorter; though you might prefer two CTEs for clarity:

  WITH table_ AS (
           SELECT DATETIME
                , TOTALTIME1
                , RAWOUTPUT1
                , CASE BITAND(RAWOUTPUT1, POWER(2,0))
                      WHEN 0 THEN 'FALSE'
                      ELSE        'TRUE' 
                  END AS Pumpe1_1 
               , CASE BITAND(LAG(RAWOUTPUT1) OVER (ORDER BY datetime), POWER(2,0))
                      WHEN 0 THEN 'FALSE'
                      ELSE        'TRUE' 
                  END AS priv_
               , CASE BITAND(LEAD(RAWOUTPUT1) OVER (ORDER BY datetime), POWER(2,0))
                      WHEN 0 THEN 'FALSE'
                      ELSE        'TRUE' 
                  END AS next_
             FROM pump_box_hist
            WHERE DATETIME > to_date('30.10.2014 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
              AND DATETIME < to_date('02.11.2014 00:00:00', 'DD.MM.YYYY HH24:MI:SS')
       )
SELECT DATETIME
     , TOTALTIME1
     , RAWOUTPUT1
     , Pumpe1_1 
  FROM table_ 
 WHERE (
                (Pumpe1_1 = next_ or next_ is null)
            AND (Pumpe1_1 <> priv_ or priv_ is null)
       )
     ;