Example: Using ITERATE to Iterate an Outer Loop - Teradata Vantage

The following example shows the use of an ITERATE statement to iterate an outer loop.

LOOP1:
WHILE hCounter > 0 
DO
   SELECT highNum INTO maxNum FROM limits
WHERE LIMIT_TYPE = 'HIGHNUM';
L1:
LOOP
      INSERT INTO transaction (trans_num,
      account_num) VALUES (hCounter, hAccountNum);
    SET hCounter = hCounter - 1;
    IF (hCounter = 10) THEN
      IF (hOnceIterated = 0) THEN
      SET hOnceIterated = 1);
        ITERATE LOOP L1;
      END IF;
    END IF;
    -- The following statement performs only if
    -- hCounter <> 10 or hOnceIterated <> 0
    SET hNum = hNum + 10;
   END LOOP L1;
   IF hCounter >= MaxNum THEN
      ITERATE LOOP1;
   END IF;
   -- The following statement performs only if
   -- hCounter < MaxNum.
      INSERT INTO transaction (trans_num,
      account_num) VALUES (hCounter, hAccountNum);
END WHILE LOOP1;
UPDATE transaction
  SET account_num = hAccountNum + 10;