am reaching out for assistance regarding a persistent SQL error I am encountering on an AS400 DB2 database when using the CASE statement within a stored procedure.

My environment details are as follows:

• Database: IBM DB2 for iSeries
• Client Tool: DBeaver

The issue arises specifically when I attempt to incorporate a CASE statement into my SELECT query within the stored procedure. The SELECT query runs flawlessly on its own, but once embedded in the stored procedure with the CASE logic, it fails.

Here is the stored procedure that triggers the error:

The error message displayed is as follows:
SQL Error [42601]: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;.

It's important to note that the SELECT statement within the procedure executes without errors when run independently of the stored procedure.

I have tried various modifications to the script without success:(

Thank you for your assistance.

------------------------------
Flora Mousavi
------------------------------

I don't have I-series handy - but some basics:

You have semicolons in the body of the procedure and also after the last END - it won't work like that with Db2 for LUW I was able to test.

Try modified script with Statement delimiter set to for example '@' like this:

Modify DBeaver settings for statement delimiters in SQL Processing tab for your script properties:

------------------------------
Jan Nelken
------------------------------

Thank you so much Jan;
I tried delimiter at the end, it doesn't make any difference;
Finally, I solved the issue by adding a new driver and running the same code I've uploaded;

Thanks again.

------------------------------
Flora Mousavi
------------------------------

Original Message:
Sent: Mon November 06, 2023 06:10 PM
From: Jan Nelken
Subject: Assistance Required: SQL CASE Statement Causes Error on DB2 for iSeries

I don't have I-series handy - but some basics:

You have semicolons in the body of the procedure and also after the last END - it won't work like that with Db2 for LUW I was able to test.

Try modified script with Statement delimiter set to for example '@' like this:

CREATE OR REPLACE PROCEDURE OUT_LANGUAGE (OUT procedureLanguage CHAR(8))

SPECIFIC SQL_OUT_LANGUAGE

DYNAMIC RESULT SETS 0

LANGUAGE SQL

READS SQL DATA

BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE errorLabel CHAR(32) DEFAULT '';

-- in case of no data found

DECLARE EXIT HANDLER FOR NOT FOUND

SIGNAL SQLSTATE value '38200' SET MESSAGE_TEXT= '100: NO DATA FOUND';

-- in case of SQL error

DECLARE EXIT HANDLER FOR SQLEXCEPTION

SIGNAL SQLSTATE value SQLSTATE SET MESSAGE_TEXT = errorLabel;

SET errorLabel = 'SELECT STATEMENT';

SELECT language INTO procedureLanguage

FROM sysibm.sysprocedures

WHERE procname = 'OUT_LANGUAGE';

END @

Modify DBeaver settings for statement delimiters in SQL Processing tab for your script properties:

------------------------------
Jan Nelken
------------------------------