Can someone guide on looping from a select statement in netezza

Question

Create or replace procedure total_test_inside()
RETURNS REFTABLE(testtabl)
LANGUAGE NZPLSQL
AS
BEGIN_PROC
DECLARE
prod_id integer;
lkp_weighted_prc numeric(20,3);
rec record;
BEGIN
FOR rec IN select prod_id from weight
LOOP
select weightprice into lkp_weighted_prc from weight;
call total_amort_test(lkp_weighted_prc);
execute immediate 'insert into ' ||REFTABLENAME || ' values(' || lkp_weighted_prc || ')';
END loop;
return REFTABLE;
END;
END_PROC;

call total_test_inside();

Can someone guide on why I'm unable to loop which is taking just last prod_id from weight table in netezza. Thanks in advance

Answer 1

I'm going to go out on a limb with what I think you're trying to accomplish here.

Since you are already doing a FOR loop here, there's no need to do another select to get weightprice, and in fact that will probably just give you the same first value over and over again.

FOR rec IN select prod_id from weight
LOOP
select weightprice into lkp_weighted_prc from weight;
call total_amort_test(lkp_weighted_prc);
execute immediate 'insert into ' ||REFTABLENAME || ' values(' || lkp_weighted_prc || ')';
END loop;

What I think you want is this. Here I've added weightprice to the select statement in the FOR definition, and removed the seemingly superfluous SELECT. Then I use "rec." qualifier to reference the single record that is stepped over in the FOR loop.

BEGIN
FOR rec IN select prod_id, weightprice lkp_weighted_prc  from weight
LOOP
call total_amort_test(rec.lkp_weighted_prc);
execute immediate 'insert into ' ||REFTABLENAME || ' values(' || rec.lkp_weighted_prc || ')';
END loop;