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?

Best Answer

  • Jim_KnicelyJim_Knicely Administrator
    edited November 24 Accepted Answer

    @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
    
    

Answers

  • Jim_KnicelyJim_Knicely Administrator

    What version of 11 are you running?

  • @Jim_Knicely said:
    What version of 11 are you running?

    Hi Jim

    Vertica Analytic Database v11.0.0-0

  • Works with this one: ....

    DO $$
    DECLARE
    field1 VARCHAR;
    field2 VARCHAR;
    field3 VARCHAR;
    field4 VARCHAR;
    counter INTEGER:=0;
    BEGIN
    FOR field1,field2,field3,field4 IN QUERY
    SELECT
      id::INTEGER
    , first_name
    , last_name
    , hire_dt
      FROM foo
      LIMIT 10
    LOOP
      RAISE NOTICE 'for returned %, %, %, %, FOUND=%',field1,field2,field3,field4, counter; counter:=counter+1;
    END LOOP;
    END;
    $$
    -- out NOTICE 2005:  for returned 1, Arthur, Dent, 2017-02-05 00:00:00, FOUND=0
    -- out NOTICE 2005:  for returned 2, Ford, Prefect, 2017-02-05 00:00:00, FOUND=1
    -- out NOTICE 2005:  for returned 3, Zaphod, Beeblebrox, 2017-02-05 00:00:00, FOUND=2
    -- out NOTICE 2005:  for returned 4, Tricia, McMillan, 2017-02-05 00:00:00, FOUND=3
    -- out NOTICE 2005:  for returned 5, Gag, Halfrunt, 2017-02-05 00:00:00, FOUND=4
    -- out NOTICE 2005:  for returned 6, Prostetnic Vogon, Jeltz, 2017-02-05 00:00:00, FOUND=5
    -- out NOTICE 2005:  for returned 7, Lionel, Prosser, 2017-02-05 00:00:00, FOUND=6
    -- out NOTICE 2005:  for returned 8, Benji, Mouse, 2017-02-05 00:00:00, FOUND=7
    -- out NOTICE 2005:  for returned 9, Frankie, Mouse, 2017-02-05 00:00:00, FOUND=8
    -- out NOTICE 2005:  for returned 10, Svlad, Cjelli, 2017-02-05 00:00:00, FOUND=9
    SELECT VERSION();                                                                                                                                                                    
    -- out                VERSION               
    -- out -------------------------------------
    -- out  Vertica Analytic Database v11.0.1-0
    
  • @marcothesane said:
    Works with this one: ....

    DO $$
    DECLARE
    field1 VARCHAR;
    field2 VARCHAR;
    field3 VARCHAR;
    field4 VARCHAR;
    counter INTEGER:=0;
    BEGIN
    FOR field1,field2,field3,field4 IN QUERY
    SELECT
      id::INTEGER
    , first_name
    , last_name
    , hire_dt
      FROM foo
      LIMIT 10
    LOOP
      RAISE NOTICE 'for returned %, %, %, %, FOUND=%',field1,field2,field3,field4, counter; counter:=counter+1;
    END LOOP;
    END;
    $$
    -- out NOTICE 2005:  for returned 1, Arthur, Dent, 2017-02-05 00:00:00, FOUND=0
    -- out NOTICE 2005:  for returned 2, Ford, Prefect, 2017-02-05 00:00:00, FOUND=1
    -- out NOTICE 2005:  for returned 3, Zaphod, Beeblebrox, 2017-02-05 00:00:00, FOUND=2
    -- out NOTICE 2005:  for returned 4, Tricia, McMillan, 2017-02-05 00:00:00, FOUND=3
    -- out NOTICE 2005:  for returned 5, Gag, Halfrunt, 2017-02-05 00:00:00, FOUND=4
    -- out NOTICE 2005:  for returned 6, Prostetnic Vogon, Jeltz, 2017-02-05 00:00:00, FOUND=5
    -- out NOTICE 2005:  for returned 7, Lionel, Prosser, 2017-02-05 00:00:00, FOUND=6
    -- out NOTICE 2005:  for returned 8, Benji, Mouse, 2017-02-05 00:00:00, FOUND=7
    -- out NOTICE 2005:  for returned 9, Frankie, Mouse, 2017-02-05 00:00:00, FOUND=8
    -- out NOTICE 2005:  for returned 10, Svlad, Cjelli, 2017-02-05 00:00:00, FOUND=9
    SELECT VERSION();                                                                                                                                                                    
    -- out                VERSION               
    -- out -------------------------------------
    -- out  Vertica Analytic Database v11.0.1-0
    

    Hello.

    The same error:

    SQL Error [10322] [0A000]: [Vertica]VJDBC ERROR: Executing FOR (QUERY) is not yet supported

  • @Jim_Knicely said:
    @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
    
    

    Thanks a lot @Jim_Knicely . We need to make a request to the production area for the upgrade.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.