Thursday, November 20, 2008

How to Loop in SQL


I recently saw a request for a For Loop executed with plain old SQL so I decided to create my own version.

The report you see was built by running the code once for each character in the name.

This required four queries: one to set it up, and one to end it. The other two are the actual code, and then one to decide whether to loop or not.

(If you want to see everything behind the scenes, set OnOff in SetUpLoop to 'On'. This will turn on feedback, verify, echo and the terminal wherever those statements are found.)

SetUpLoop creates a temp table to hold the Counter and then calls the query that determines where we are in the loop.

SetUpLoop.SQL
Define OnOff = Off
Set Verify &OnOff Feedback &OnOff Echo &OnOff

Create Global Temporary Table GTT_CountHolder 
  On Commit Preserve Rows
  As
      Select 1 As Counter 
        From Dual;

@Looper

Looper uses case statements to set two amper-variables. They are both used with the '@' sign to first either run the code or, at the end drop the temp table. The second tells the system to call this same query again, or just drop out.

The terminal is turned off unless debugging so the output from this query is not shown to the user.

Looper.SQL
Set Term &OnOff

Column RunProg1 New_Value Run_Code_Or_End
column RunProg2 New_Value Run_Loop_Or_Stop

Select         Case When Counter <= (Select Length(Ename)
                                       From Emp
                                      Where Rownum <2)
                    Then 'Code'
                    Else 'EndLoop'
                 End 
               As
       RunProg1,
       --------------------------------------------------
               Case When Counter <= (Select Length(Ename)
                                       From Emp
                                      Where Rownum <2)
                    Then 'Looper'
                    Else ' '
                 End 
               As
       RunProg2
  From GTT_CountHolder ;

Set Term On

@&Run_Code_Or_End

@&Run_Loop_Or_Stop

Code is the actual code that we want to run multiple times. It runs the query and then updates the Counter in the temp table.

Code.SQL
Column Character Format A9

Select Ename , 
       -------------------------------
       -- Show position
       -------------------------------
                (Select Counter 
                  from GTT_CountHolder )
                As
       Position,
       --------------------------------
       -- Show character
       -- Substr(field,position,length)
       --------------------------------
                Substr(Ename                       /* Field    */  
                      , (Select Counter 
                           from GTT_CountHolder )  /* Position */  
                      ,1)                          /* Length   */
                As
       Character         
       -------------------------------
  From Emp
 Where Rownum < 2;

Update GTT_CountHolder 
   Set Counter = Counter + 1;

EndLoop is just a little clean-up.

EndLoop.SQL
Truncate Table GTT_CountHolder ;
    Drop Table GTT_CountHolder ;
Posted by Lynn Tobias at 10:19 AM
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)