bluish bluish - 5 months ago 24
SQL Question

Error SQL0104 when creating a function in System i V7R1

I'm creating a SQL function on System i V7R1:

CREATE FUNCTION MYSCHEMA.GROUPDIBAS(v_code VARCHAR(50))
RETURNS VARCHAR(2048)
LANGUAGE SQL
BEGIN
DECLARE str VARCHAR(2048);
SET str = '';
FOR row AS (
SELECT
FIELD2
FROM MYSCHEMA.DIBAS
WHERE FIELD1 = v_code
)
DO
SET str = 'Bubi'; --I removed many statements to make clear the problem doesn't come from them
END FOR;
RETURN str;
END
;


I execute it with "Run SQL script" tool, which is part of the iSeries Navigator V7R1.
It works on another V7R1 server (using iSeries Navigator V5R4), but not in that one where I'm working now. It fails with this message:

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;.
Cause . . . . . : A syntax error was detected at token <END-OF-STATEMENT>.
Token <END-OF-STATEMENT> is not a valid token. A partial list of valid tokens is ;.
This list assumes that the statement is correct up to the token.
The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point.
Recovery . . . : Do one or more of the following and try the request again:
-- Verify the SQL statement in the area of the token <END-OF-STATEMENT>. Correct the statement.
The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses.
-- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.


If I remove the
FOR
block, it works.

Moreover if I execute the statement with 5250 Emulator, command STRSQL, it works. So it seems like a bug in "Run SQL script" client.

Any hint will be appreciated!

Answer

Given the tests made by @JamesA and me, I fear the problem can be in the Program Temporary Fix (PTF) that this server hasn't and the other ones have. Specifically, running WRKPTFGRP command, I can guess it probably misses this PTF group:

PTF group  Level  Text
SF99701        5  DB2 FOR IBM I

Unfortunately I can't try installing it now :(.