The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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
379243342
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: 359 times

last updated: 05 Jul '14, 10:17