Db2

 View Only
Expand all | Collapse all

Assistance Required: SQL CASE Statement Causes Error on DB2 for iSeries

  • 1.  Assistance Required: SQL CASE Statement Causes Error on DB2 for iSeries

    Posted Mon November 06, 2023 08:29 AM

    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:


    CREATE PROCEDURE ZRDTA9999.GetMStop(in nanoParam CHAR(3))
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    BEGIN
        DECLARE c1 CURSOR WITH RETURN FOR
           SELECT
                RTRIM(LTRIM(NH.X1NANO)) AS National_Account_Number,
                RTRIM(LTRIM(NM.X7CMP)) AS Plant,
                RTRIM(LTRIM(LN.X2LNAM)) AS Location,
                RTRIM(LTRIM(NM.X7MSTP)) AS Master_Stop,
                RTRIM(LTRIM(TM.AYMSNM)) AS Company_Name,
                RTRIM(LTRIM(TM.AYORDR)) AS PO_Ref,
                RTRIM(LTRIM(TM.AYBCHI)) AS Inv_Cycle,
                CASE
                    WHEN (SELECT COUNT(1)
                          FROM TRDTA2401.TRSCTL AS TC
                          WHERE TC.ALMSTP = TM.AYMSTP
                            AND TC.ALCMP = TM.AYCMP
                            AND TC.ALSSTS IN ('1', '2', '3')) > 0 THEN 'Y'
                    ELSE 'N'
                END AS Active
            FROM
                xxxx.NAHEAD AS NH
            JOIN
                xxxx.NAMSTP AS NM ON NH.X1NANO = NM.X7NANO
            JOIN
                xxxx.NALOCN AS LN ON NM.X7CMP = LN.X2CMP
            JOIN
                tttt.TRMSTP AS TM ON NM.X7MSTP = TM.AYMSTP
            WHERE
                NH.X1NANO = nanoParam;
     
        OPEN c1;
    END;




    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
    ------------------------------


  • 2.  RE: Assistance Required: SQL CASE Statement Causes Error on DB2 for iSeries

    Posted Mon November 06, 2023 06:11 PM

    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
    ------------------------------

    Original Message


  • 3.  RE: Assistance Required: SQL CASE Statement Causes Error on DB2 for iSeries

    Posted Tue November 07, 2023 03:47 AM

    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