CODEWITHSUNDEEP
sqlplsqldb2
Bogdan Lashkov
Bogdan Lashkov

Reputation: 359

Does PLSQL in DB2 support nested while statemnts?

I have a following query for DB2 database:

    BEGIN
        DECLARE NUMBER_OF_ROWS INT;
        DECLARE ITER_INDX_1 INT;
        DECLARE ITER_INDX_2 INT;
        SET ITER_INDX_1 = 0;
        SET ITER_INDX_2 = 0;
        SET NUMBER_OF_ROWS = (SELECT COUNT(*) FROM LOOPS.LTV_BEISPIEL);
        WHILE ITER_INDX_1 < NUMBER_OF_ROWS DO
            WHILE ITER_INDX_2 < NUMBER_OF_ROWS DO
                INSERT INTO LOOPS.TEST VALUES (1);
                SET ITER_INDX_2 = ITER_INDX_2 + 1;
            END WHILE;
            SET ITER_INDX_1 = ITER_INDX_1 + 1;
        END WHILE;
    END
    ;

NUMBER_OF_ROWS variable id set to 10 after querying the COUNT statement. As there are two WHILE statement, and one of them is nested, this code must produce 10*10=100 inserts, but for some reason it does only 10 inserts. Is there is an error in my logic or it is a bug in DB2?

Upvotes: 0

Views: 253

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269893

You are not resetting ITER_INDX_2 after the first loop:

        SET ITER_INDX_1 = ITER_INDX_1 + 1;
        SET ITER_INDX_2 = 0;

Hence, to answer your question: This is a bug in your code.

To be honest, I would do this at the top of the loop:

   WHILE ITER_INDX_1 < NUMBER_OF_ROWS DO
        SET ITER_INDX_2 = 0;
        WHILE ITER_INDX_2 < NUMBER_OF_ROWS DO
            INSERT INTO LOOPS.TEST VALUES (1);
            SET ITER_INDX_2 = ITER_INDX_2 + 1;
        END WHILE;
        SET ITER_INDX_1 = ITER_INDX_1 + 1;
    END WHILE;

Upvotes: 1

Related Questions