Snowflake Nested Loop loop - Why is this code not starting a new iteration of the outer loop?-CodePudding

This query is successfully starting the first iteration of the outer loop, and then completing the first inner loop iteration (10), but is not moving on to the second outer loop iteration.

execute immediate $$
begin
CREATE OR REPLACE Temporary TABLE tmploop (ID INT IDENTITY, Counter INT, slotCounter INT);
  let counter := 1;
  let countermax := 5;
  let slotcounter :=1;
  let maxslots := 10;
  let outercounter := 1;
  loop
   if (counter <= countermax) then
        let slotcounter :=1;
        //counter := counter   1;
  SET outercounter := outercounter  1;
   end if;
    loop
    if (slotcounter <= maxslots) then
        
        INSERT INTO tmploop (counter, slotcounter) 
        VALUES(:counter, :slotcounter);
        slotcounter := slotcounter   1;
    continue outer;
    else 
    //counter := counter   1;
    break inner;
     end if;
       end loop inner;
    counter := counter   1;
    break;
  end loop outer;
  return array_construct(counter, slotcounter, outercounter);
end;
$$;

I've tried moving around the continue outer, break outer, removing the break, etc, but it seems everthing aside from the above code results in an infinite loop.

CodePudding user response：

here is your code with spaces amount the actions:

execute immediate $$
begin
    CREATE OR REPLACE Temporary TABLE tmploop (ID INT IDENTITY, Counter INT, slotCounter INT);

    let counter := 1;
    let countermax := 5;
    let slotcounter :=1;
    let maxslots := 10;
    let outercounter := 1;
    loop
        if (counter <= countermax) then
            let slotcounter :=1;
            //counter := counter   1;
            SET outercounter := outercounter  1;
        end if;
    
        loop
            if (slotcounter <= maxslots) then
        
                INSERT INTO tmploop (counter, slotcounter) 
                    VALUES(:counter, :slotcounter);
                    
                slotcounter := slotcounter   1;
                continue outer;
            else 
                //counter := counter   1;
                break inner;
            end if;
            
        end loop inner;
        
        counter := counter   1;
        
        break; -- THIS LINE IS THE PROBLEM
        
    end loop outer;
    
    return array_construct(counter, slotcounter, outercounter);
end;
$$;

that BREAK, it say, just before I do this loop again, stop looping.

So to answer the question, "why does it stop looping" because you told it too.

Like many problems loops should be avoided for inserting incremental data, and the problem should most likely be inverted, and ether a Recursive CTE or some other pattern used to generate the data you wish for.

The code as it stands loops forever, so that needs solving. If it was bonded at worked as expect offering methods to invert it could happen, but as it stands.. it is a little mysterious.

CodePudding user response：

It needs the break, but needed to move the outer end if to the bottom. The below code works great.

execute immediate $$

begin

CREATE OR REPLACE Temporary TABLE tmploop (ID INT IDENTITY, Counter INT, slotCounter INT);

let counter := 1;

let countermax := 5;

let slotcounter :=1;

let maxslots := 10;

let outercounter := 1;

loop

if (counter <= countermax) then

let slotcounter :=1;

//counter := counter 1;

SET outercounter := outercounter 1;

loop

if (slotcounter <= maxslots) then

 INSERT INTO tmploop (counter, slotcounter) 

 VALUES(:counter, :slotcounter);

 slotcounter := slotcounter   1;

--we don't need the continue outer here. else

 //counter := counter   1;

 break inner;

end if;

end loop inner;

counter := counter 1;

else

break; --definitely need to end the loop after all iterations of the outer loop are satisfied.

end if;

end loop outer;

return array_construct(counter, slotcounter, outercounter);

end;

$$;