The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
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?
Tagged:
0
Best Answer
-
Jim_Knicely - Select Field - 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
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.