Error when executing a stored procedure
Hello, We are developing some stored procedures at Version 11. In the code we call a FOR QUERY statement, FOR field1,field2,field3,field4 IN QUERY SELECT field1,field2,field3,field4 FROM sometable LOOP RAISE NOTICE 'for returned %, %, %, %, FOUND=%',field1,field2,field3,field4, counter; counter:=counter+1; END LOOP;
The stored was created without problem, but when it's executed throw the next error:SQL Error [10322] [0A000]: [Vertica][VJDBC](10322) ERROR: Executing FOR (QUERY) is not yet supported
The documentation said this statement is supported, someone can we help us?
Best Answer
-
Jim_Knicely Administrator
@ramon_perea - The abillity to use a FOR QUERY in Vertica was introduced in 11.0.0-1.
Example:
dbadmin=> SELECT version(); version ------------------------------------- Vertica Analytic Database v11.0.0-1 (1 row) dbadmin=> SELECT * FROM foo; id | first_name | last_name | hire_dt ----+------------+-----------+------------ 1 | Jim | K | 2021-11-24 2 | Jane | L | 2021-11-24 3 | Josh | K | 2021-11-24 (3 rows) dbadmin=> DO $$ dbadmin$> DECLARE dbadmin$> field1 VARCHAR; dbadmin$> field2 VARCHAR; dbadmin$> field3 VARCHAR; dbadmin$> field4 VARCHAR; dbadmin$> counter INTEGER:=0; dbadmin$> BEGIN dbadmin$> FOR field1,field2,field3,field4 IN QUERY dbadmin$> SELECT dbadmin$> id::INTEGER dbadmin$> , first_name dbadmin$> , last_name dbadmin$> , hire_dt dbadmin$> FROM foo dbadmin$> LIMIT 10 dbadmin$> LOOP dbadmin$> RAISE NOTICE 'for returned %, %, %, %, FOUND=%',field1,field2,field3,field4, counter; counter:=counter+1; dbadmin$> END LOOP; dbadmin$> END; dbadmin$> $$ dbadmin-> ; NOTICE 2005: for returned 1, Jim, K, 2021-11-24, FOUND=0 NOTICE 2005: for returned 2, Jane, L, 2021-11-24, FOUND=1 NOTICE 2005: for returned 3, Josh, K, 2021-11-24, FOUND=2 DO
0
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Answers
What version of 11 are you running?
Hi Jim
Vertica Analytic Database v11.0.0-0
Works with this one: ....
Hello.
The same error:
SQL Error [10322] [0A000]: [Vertica]VJDBC ERROR: Executing FOR (QUERY) is not yet supported
Thanks a lot @Jim_Knicely . We need to make a request to the production area for the upgrade.