SAP SQL Anywhere

Question

I have a small problem that I need help with.

I have a loop that adds the values into a table from a store procedure that I use in a select. The SP can return one or more rows. t_rownr tells which row it is. It always starts at 1.This is working properly. The problem is that I have a loop that is outside it. If ct_max value is greater than 1, it shall repeat the select and insert. This is where the problem comes. My select returns the same value, but I want it to increase t_rownr each time the loop is executed.

Example If ct_max = 4:

    Select returns
    t_rownr --- t_type 
    ---------------------
    1------------ER12
    2------------ES53
    3------------EC81


    First insert
    c_rownr --- c_type 
    ---------------------
    1------------ER12
    2------------ES53
    3------------EC81

    Second insert
    c_rownr --- c_type 
    ---------------------
    4------------ER12
    5------------ES53
    6------------EC81

    Third insert
    c_rownr --- c_type 
    ---------------------
    7------------ER12
    8------------ES53
    9------------EC81

    Fourth insert
    c_rownr --- c_type 
    ---------------------
    10------------ER12
    11------------ES53
    12------------EC81

This may not be the right or best way to clone lines and increase t_rownr every time the clone?

I've tried to add this to enumerate the line number (t_rownr + ct_counter) -1. It works if the select returns only one row, but not when it retunerar several rows.

SET ct_counter = 1;                 
    WHILE ct_counter <= ct_max LOOP 
                      INSERT INTO tblTest(c_rownr, c_type) 
                SELECT (t_rownr + ct_counter) -1 ,t_type from sp_calValue(234,33)
                    SET ct_counter = ct_counter + 1;
                END LOOP;

Someone who can help me solve this?

Accepted Answer

This is not the optimal way because of database performance concerns, but I used select count + 1 to determine the next row number for each insert statement.

begin
declare ct_max integer;
declare ct_counter integer;
declare local temporary table tblTest (c_rownr integer, c_type long varchar);

SET ct_max = 10;
SET ct_counter = 1;
WHILE ct_counter <= 5 LOOP--ct_max LOOP
INSERT INTO tblTest(c_rownr, c_type)
SELECT (SELECT COUNT(*)+1 FROM tblTest) as c_rownr,
(SELECT 'TEST') as c_type; --t_type FROM sp_calValue(234,33) as c_type);
SET ct_counter = ct_counter + 1;
END LOOP;
SELECT * from tblTest order by c_rownr;
end;

SAP SQL Anywhere

Loop and increase values?

Follow this question

Related questions