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

You can't use PL/SQL while, loop and if constructs in plain SQL. Why aren't you just filtering the dates inside your table_ CTE? Because the first and last values in the range would then not lag/lead values to set their priv_ and next_? — Alex Poole
– Alex Poole, Commented Feb 27, 2015 at 9:41
@AlexPoole The line numbers are from the original post. Applied to the unformatted code fragment that would have been the last 2 lines. I agree with you that actually the first two lines should have been reported. However, I have repeatedly experienced oracle spewing syntax error messages in the reverse order of lexical occurrence. Hard to tell what's actually been printed, even more so as it doesn't seem to be the original error message ( ora-xxxxx id is missing ). Your guess about the IDE origin makes sense. — collapsar
– collapsar, Commented Feb 27, 2015 at 9:52
@collapsar - well, no, you were still right; took the radical step of trying to run it, and SQL Developer did give 'unknown error' for lines 1, 2, 11 and 12 - so the OP's list was incomplete anyway. I withdraw my scepticism, and I've rolled back to your original edit *8-) — Alex Poole
– Alex Poole, Commented Feb 27, 2015 at 10:03

Alex Poole · Accepted Answer · 2015-02-27 10:41:22Z

2

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)
       )
     ;

edited Feb 27, 2015 at 10:41

answered Feb 27, 2015 at 10:11

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kᴀτᴢ Over a year ago

Thanks man, this brought my query from 50 down to 19 seconds (it's a shame I didn't see to bring the WHERE to the first query...). In the database are around 300k rows, so I thought it would be possible to break the query with the LOOP if DATETIME is first time bigger than the given date (already ordered by DATETIME). Would this somehow be possible to save more time?

Alex Poole Over a year ago

@katz - that's effectively what the filter in the CTE is doing, sort of. It's selecting the set of records from your table that are in your date range, and then only doing any work against that smaller set. Your original query was doing the bitand calculation and the lead/lag against all 300k rows, and then filtering at the end, which was doing much more than necessary, hence being slower. Your while construct would have given different results anyway I think, if it had worked at all - you would have ended up with lots of single-row queries which couldn't see adjacent results for lead/lag.

Collectives™ on Stack Overflow

Oracle while loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related