Stored procedure Missing/Invalid SQL statement'E(3707) error

Database

Stored procedure Missing/Invalid SQL statement'E(3707) error

SPL1027:E(L155), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like ';' between a string or a Unicode character literal and '|'.'.

SPL1030:E(L156), Referring to undefined cursor 'stmt1'.

SPL1027:E(L157), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like an 'END' keyword between ';' and the 'EXECUTE' keyword.'.

Hi all, i have this code and can't find why i get previous errors:

     DECLARE lista_entrada VARCHAR(100) DEFAULT 'entrada';     

     DECLARE lista_salida VARCHAR(100) DEFAULT 'salida';

     DECLARE tel DECIMAL(12,0);

     DECLARE rut DECIMAL(12,0);

     DECLARE id INTEGER;

     DECLARE cont CURSOR FOR

       SELECT AREA, RUT, ID

       FROM cntTable

       FOR READ ONLY;

      OPEN cont;

       --LISTA DE LINEAS RETENIDAS

       REPEAT

         BEGIN

           FETCH cont INTO tel, rut, ps;

             SET sql_stmt_cont = 'UPDATE cntNextTable SET PROD_ENTRADA = ' | lista_entrada | ', PROD_SALIDA = ' | lista_salida | ' WHERE AREA = ' | tel | ' AND RUT = ' | rut | ' AND ID = ' | id;

             PREPARE stmt1 FROM sql_stmt_cont;

             EXECUTE stmt1;

           END;

        UNTIL (SQLCODE = 0)

        END REPEAT;

       --END WHILE;

      CLOSE cont;

any help will be very appreciated.

Tags (1)
3 REPLIES
Junior Contributor

Re: Stored procedure Missing/Invalid SQL statement'E(3707) error

String concatenation is done with two not one pipe character:

SET sql_stmt_cont = 'UPDATE cntNextTable SET PROD_ENTRADA = ' || lista_entrada ||...

But why do you use a cursor to do multiple updates instead of one direct SQL UPDATE cntNextTable FROM UPDATE cntNextTable ...?

And why do you REPEAT ... UNTIL (SQLCODE = 0)?

Re: Stored procedure Missing/Invalid SQL statement'E(3707) error

From the Teradata manual, but that is solved, thx Dieter.

About your question "But why do you use a cursor to do multiple updates instead of one direct SQL UPDATE cntNextTable FROM UPDATE cntNextTable ...?"

I have a table with a list of client codes in a table:

client_code client_name

1 john

2 peter

3 nathan

...

I have another table with a list of (more than one) products associated to each client code:

client_code  prod_code

1 bread

1 cookies

1 butter

2 butter

2 bread

3 cookies

3 bread

...

I have another 3rd table with the clients and an empty field:

client_code   product_list

1  ?

2  ?

3  ?

...

that requieres to be filled the next way:

client_code   product_list

1 'bread+cookies+butter'

2 'butter+bread'

3 'cookies+bread'

...

I've tried to do the next way:

1.- A cursor whith client_list

2.- another cursor inside the previous one, to get productlist for client to do concatenation into a string variable.

3.- A DBC.SysExecSQL(sql_stmt_cont) statement to do each update.

the code is :

     DECLARE cont CURSOR FOR

       SELECT client_code

       FROM client_list

       FOR READ ONLY;

      DECLARE list CURSOR FOR query_list;

      DECLARE updt CURSOR WITH RETURN ONLY FOR query_upd;

      OPEN cont;

          WHILE (SQLCODE=0) DO

            FETCH cont INTO client_code

             SET sql_stmt_list = 'SELECT prod_code FROM client_product WHERE client_code = '|| client_code ||';';

             PREPARE query_list FROM sql_stmt_list;

             OPEN list;

                --LISTA DE PRODUCTOS POR LINEA

                WHILE (SQLCODE=0) DO

                  FETCH list INTO product_code

                       SET list_p = list_p || '+' || product_code;

                END WHILE;

              CLOSE list;

                BEGIN

                    DECLARE sql_stmt_cont VARCHAR(500);

                    SET sql_stmt_cont = 'UPDATE prod_client SET product_list = ''' || list_p || ''' WHERE client_code  = ' || client_code || ';';

                    CALL DBC.SysExecSQL(sql_stmt_cont);

                END;

        END WHILE;

      CLOSE cont;

and the error is:

SPL5009:E(L42), Dynamic cursor 'list' is not allowed with WITHOUT RETURN, WITH RETURN TO CALLER/CLIENT.

Please help!

Junior Contributor

Re: Stored procedure Missing/Invalid SQL statement'E(3707) error

Cursors are evil in a parallel database system and nested cursor are even worse.

Why don't you create you delimited list using MAX(CASE) or recursion, there have been multiple threads on that topic, e.g. two days ago:

http://forums.teradata.com/forum/general/doubt-with-rank-over-partition-by-function

If you're on TD14 and got access to the TDStats.udfconcat function it's a simply aggregation.