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.

Define OnOff = Off
Set Verify &OnOff Feedback &OnOff Echo &OnOff

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


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.

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'
Case When Counter <= (Select Length(Ename)
From Emp
Where Rownum <2)
Then 'Looper'
Else ' '
From GTT_CountHolder ;

Set Term On



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.

Column Character Format A9

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

Update GTT_CountHolder
Set Counter = Counter + 1;

EndLoop is just a little clean-up.

Truncate Table GTT_CountHolder ;
Drop Table GTT_CountHolder ;

No comments:

Post a Comment