On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

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 ;

No comments:

Post a Comment