Skip to Content
avatar image
Former Member

dbproc with while in while

In a dbproc a want to use two while statements to fill a table.

But only the inner while is executed correctly, the outer while only runs one time.

The table below only contains values for JAHR=2001.

CREATE TABLE LM_T2 (

JAHR Integer,

LEB_ALTER Integer,

GESAMT Integer,

ABGAENGE Integer)

create dbproc proc_lm_fill_aquotber as

var startjahr int; endjahr int; startalter int; endalter int;

set startjahr = 2001;

set endjahr = 2008;

set startalter = 20;

set endalter = 80;

while startjahr < endjahr do

begin

while startalter < endalter do

begin

insert into dba.lm_t2 (jahr, leb_alter) values (:startjahr, :startalter);

set startalter = startalter +1;

end;

set startjahr = startjahr +1;

end;

If I put the inner while into a separate procedure I get the expected results.

But shouldn't the above procedure work ?

Elke

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Sep 01, 2008 at 03:14 PM

    Hi Elke,

    yes, your code should work as expected, but does not 😔

    There seems to be a bug concerning the handling of nested statements.

    As a workaround you may just use one loop with seven insert statements (one for each year).

    regards,

    Lars

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 08:31 AM

    Hi Elke,

    sorry, but the db-procedure behaves as expected. This is because you do not initialize startalter inside the outer loop. The corrected version looks as follows :

    CREATE TABLE LM_T2 (

    JAHR Integer,

    LEB_ALTER Integer,

    GESAMT Integer,

    ABGAENGE Integer)

    create dbproc proc_lm_fill_aquotber as

    var startjahr int; endjahr int; startalter int; endalter int;

    set startjahr = 2001;

    set endjahr = 2008;

    set endalter = 80;

    while startjahr < endjahr do

    begin

    set startalter = 20;

    while startalter < endalter do

    begin

    insert into dba.lm_t2 (jahr, leb_alter) values (:startjahr, :startalter);

    set startalter = startalter +1;

    end;

    set startjahr = startjahr +1;

    end;

    Best Regards,

    Thomas

    Add comment
    10|10000 characters needed characters exceeded