FETCH NEXT statement

Database
Enthusiast

FETCH NEXT statement

Hello,
I am using a Cursor in a stored procedure.
In the procedure, I have a while statement , which inserts rows in a volatile table.
It then fetches the next values in the cursors & loops back depending on the condition fo the while loop.

I get an error when I add the two fetch next statements in the while loop.If the two FETCH NEXT statements are removed , the cursor compiles without any errors.
What can I do ?

WHILE ( condition ) DO
INSERT statement;
FETCH NEXT;
FETCH NEXT
END WHILE;

The above gives an error. However if i remove the fetch statements , it compiles.
The following are the errors;

SPL1135:E(L116), Invalid fetch orientation specified in the Fetch statement.

SPL1033:E(L116), Unexpected text 'into'.

SPL1027:E(L116), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like ';' between the 'into' keyword and the word 'emp_val'.

SPL1135:E(L117), Invalid fetch orientation specified in the Fetch statement.

SPL1033:E(L117), Unexpected text 'into'.

SPL1027:E(L117), Missing/Invalid SQL statement'E(3707):Syntax error, expected something like ';' between the 'into' keyword and the word 'emp_count'.

Thank you already ...

Regards,
az_maverick
3 REPLIES
Senior Apprentice

Re: FETCH NEXT statement

Could you please post the actual code?
Looks like a missing semicolon...

Btw, cursors used for inserts are really bad, this is not Oracle, better rewrite it as an SQL insert/select.

Dieter
Enthusiast

Re: FETCH NEXT statement

Hello,
Thank you for your reply.
I nede to return a result set which is not possible in TD sql. So I create a volatile table & insert the results of the complex SQL query in this volatile table.
is it possible to do the above in some other way ?

The cursor is primarily used , because I need to pick up values for the where clause in the complex query .So I am using the cursor as a pointer to these values...

Mind you these values are volatile & change daily .. hence I cannot have a static where clause ... The complex sql query and the cursor takes care of it...
I will post the code that it is possible for you to detect errors in my code ...

Thanks,
az_maverick
Teradata Employee

Re: FETCH NEXT statement

Hello,

It is not required for a WHERE clause to be always static, it can be dynamic as well!

And as suggested by Dieter, actual code is the requirement to solve this issue!

Regards,

Adeel