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?

asked 04 Jul '14, 16:51

Rolle's gravatar image

Rolle
375213041
accept rate: 0%

edited 04 Jul '14, 16:59


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;

permanent link

answered 05 Jul '14, 10:17

harncw's gravatar image

harncw
106459
accept rate: 25%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 04 Jul '14, 16:51

question was seen: 309 times

last updated: 05 Jul '14, 10:17