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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…