Assign select result to variable in Netezza stored procedure

Now THAT is a lengthy title for a blog post.

Am currently working on stored procedure to calculate Dijkstra's shortest path when I ran into this problem (as stated above).

Looked through Netezza's Stored Procedure guide but couldn't find anything of use (perhaps I was not looking hard enough.

Unfortunately for me even more when most of the SQL-variants out there also couldn't point me in the right direction (even PostgresSQL!)

I blame you for not being able to sleep tonight!

After examining the error code in Aginity multiple times, I tried to infer that the INTO probably had to be put after the statement, since the error message was complaining something about not being able to do select a variable before doing an INTO.

So what if the variable was put after the INTO?

Maybe even after the whole statement itself.


DECLARE
vID varchar;
vESTIMATE integer;
...
...
select '5','8'--id , estimate,
from 
(
select row_number() over (order by estimate) row_num,id, estimate 
from SNA_TEMP_PATH
where done = 0
order by estimate
) A
 where A.row_num =1
into vID,vESTIMATE;

RAISE NOTICE 'vID now is %',vID;
RAISE NOTICE 'vESTIMATE now is %',vESTIMATE;

And it worked. :)

Comments

Popular posts from this blog

HIVE: Both Left and Right Aliases Encountered in Join

Splitting value in Netezza using array_split