I've a row as below.
Now, I've to create a column 'No._Pymnts' for each row, whose derivation is as below.
set No._Pymnts =0;
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.
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'
SET lock_user = lock_loop.UserName;
CALL "DBC".SysExecSQL('MODIFY USER ' || :lock_user || ' AS PROFILE=LOCKOUT_PROFILE;');
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.