Skip to content
Advertisement

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?

Advertisement

Answer

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;

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement