CODEWITHSUNDEEP
loopswhile-loopgoogle-bigquery
Olivier Thierie
Olivier Thierie

Reputation: 171

Standard SQL - Not able to loop over main loop

When using nested LOOP or WHILE loop in bigquery, it seems I am not able to iterate again over the outer loop. The problem can be reproduced with the code below.

DECLARE i int64 DEFAULT 0;
DECLARE j int64 DEFAULT 0;
DECLARE k int64 DEFAULT 0;
WHILE i < 3 DO
  SET i = i + 1;
  WHILE j < 2 DO
    SET j = j + 1;
    IF j = 2 THEN
      SET k = k+7;
    END IF;
    EXECUTE IMMEDIATE  """
      WITH test AS(SELECT @i2 AS i, @j2 AS j, @k2 AS k)
      SELECT * FROM test
    """
    USING i AS i2, j AS j2, k AS k2;
  END WHILE;
END WHILE;

As output of this, Bigquery give me back two iterations (the inner loop):

Row i j k
1 1 1 0
Row i j k
1 1 2 7

I would expect that, when we end the inner while loop, we would go to the outer one and start over. Ending up in something like:

Row i j k
1 2 2 7

What is the right way to do this? When using the same set-up but with a LOOP and BREAK condition, the results are exactly the same as explained as above. when using CONTINUE instead of BREAK my query runs forever / keeps hanging at the second statement

Upvotes: 1

Views: 664

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

I would expect that, when we end the inner while loop, we would go to the outer one and start over

It actually performs exactly as you expected - to check this - run below with extra line so you will see the proof

DECLARE i int64 DEFAULT 0;
DECLARE j int64 DEFAULT 0;
DECLARE k int64 DEFAULT 0;
WHILE i < 3 DO
  SET i = i + 1;
  SELECT i; # insert this line to check correctness
  WHILE j < 2 DO
    SET j = j + 1;
    IF j = 2 THEN
      SET k = k+7;
    END IF;
    EXECUTE IMMEDIATE  """
      WITH test AS(SELECT @i2 AS i, @j2 AS j, @k2 AS k)
      SELECT * FROM test
    """
    USING i AS i2, j AS j2, k AS k2;
  END WHILE;
END WHILE;

So, obviously for i = 2 - WHILE j < 2 DO evaluated as false and thus skipped

What is the right way to do this?

It depends on what you are trying to achieve - but usually this is done by resetting j inside first loop as in below example

DECLARE i int64 DEFAULT 0;
DECLARE j int64 DEFAULT 0;
DECLARE k int64 DEFAULT 0;
WHILE i < 3 DO
  SET i = i + 1;
  SET j = 0; # reset j
  WHILE j < 2 DO
    SET j = j + 1;
    IF j = 2 THEN
      SET k = k+7;
    END IF;
    EXECUTE IMMEDIATE  """
      WITH test AS(SELECT @i2 AS i, @j2 AS j, @k2 AS k)
      SELECT * FROM test
    """
    USING i AS i2, j AS j2, k AS k2;
  END WHILE;
END WHILE;

Upvotes: 2

Related Questions