amazon web services - Nested While Loop in Redshift

Question

My Table_1 looks like this

Parent_Id   Child_Id    Product Prod_count
1000          1            A    1
1000          2          A+B    1
1000          3            A    1
1000          4          B+C    1
2000          1            A    1
2000          2          B+C    1
2000          3            C    1
2000          4            D    1

I am trying to do Nested Loop here in this procedure,Loop based on Parent_Id, Each parent has different child so that also has to be read each row.

I have tried this

create or replace procedure sp_dummy(IN var1 int, IN var2 int, IN var3 int) as $$

Begin

create temp table find_id as(  
      select distinct parent_id,row_number() over(order by 1) as rw_num
      from table_1
);


declare 
  tot_cnt int := (select count(distinct parent_id) from find_id );
  init_loop int := 1;
  in_init_loop int := 1;
  in_tot_init_loop int;
  v_parent_id int;

Begin
  While init_loop <= tot_cnt    
  Loop
  Raise info 'init_loop = %', Init_loop;  
  Execute 'Select parent_id into ' || v_parent_id  || ' from find_id where rw_num = ' || Init_loop;
  Raise info 'v_patient_id = %', v_patient_id;
  
  Execute 'Select Count(*) into ' || in_tot_init_loop  || ' from Table_1 where Parent_Id = ' || v_parent_id;
  
  While in_init_loop <= in_tot_init_loop
  Loop
  Raise info 'in_init_loop = %', in_init_loop;
  in_init_loop = in_init_loop + 1
  End loop;
  
  init_loop = init_loop + 1;
  end loop;     
  
End; 
End;
$$ language plpgsql;

On trying this I am getting error Cannot Execute a Null Query string I gave up on trying understanding this error!! :(

question from:https://stackoverflow.com/questions/65922582/nested-while-loop-in-redshift

Answer 1

This line seems problematic:

Execute 'Select parent_id into ' || v_parent_id  || ' from find_id where rw_num = ' || Init_loop;

The v_parent_id is empty, so it would translate into:

Select parent_id into NULL from find_id where rw_num = 1;

I think you actually wanted to write:

SELECT INTO v_parent_id
  parent_id
FROM find_id
WHERE rw_num = Init_loop;

Yes, you can actually put the SQL in-line, rather than having to pass it as a string to EXECUTE. Take a look at the examples in Structure of PL/pgSQL - Amazon Redshift.