Looping in a row

Database
Enthusiast

Looping in a row

Hi,

  I've a row as below.

Bill_stdt, Bill_enddt,Bill_cycl

1-jan,30-mar,weekly

....

Now, I've to create a column 'No._Pymnts' for each row, whose derivation is as below.

set temp_dt=Bill_stdt;

set No._Pymnts =0;

while temp_dt<=bill_enddt,

temp_dt=temp_dt+Bill_cycl;

No._Pymnts =No._Pymnts+1;

As per my understanding, While loop can be used only for the variables.

As we have to derive No._Pymnts field for each row present in table , Can anyone let me know how can this be achieved in SQL.

1 REPLY
Enthusiast

Re: Looping in a row

I'm assuming you are using a stored procedure. I'm not familiar with the WHILE loop syntax, but I may be able to point you in the right direction.

You can use a FOR loop to loop through each one of your rows. You should be able to set your variables and use the calculation inside of the loop.

Here's an example where I execute some SQL to lock users based on some criteria:

FOR lock_loop AS lock_cur CURSOR FOR 
SELECT UserName FROM DBA_DATA.UserHealthLog
WHERE AlertDate = :CURR_DATE
AND AccountLocked = 'Y'
DO
SET lock_user = lock_loop.UserName;
CALL "DBC".SysExecSQL('MODIFY USER ' || :lock_user || ' AS PROFILE=LOCKOUT_PROFILE;');

END FOR;

Of course, I could reference lock_loop.UserName directly in my SQL instead of using SET. In the SQL above, the variable "lock_loop" refers to a single row in the result set from the query... as such, it is a single value.