CODEWITHSUNDEEP
sqlstored-proceduresdb2
Luat Phan
Luat Phan

Reputation: 3

Unexpected token "<variable declaration>" was found following ""

Im a newbie to DB2. Im trying to convert this sp from Microsoft SQL server to DB2 Here is the sp from Microsoft SQL:

    DECLARE @DELIMITER VARCHAR(1) =','
    DECLARE  @FILTER_TYPE VARCHAR(128) ='Animal_ID_17'
    DROP TABLE IF EXISTS #tmp_Inputs
    CREATE TABLE #tmp_Inputs
    (
     INPUT_VALUE VARCHAR(128)
    )

    INSERT INTO #tmp_Inputs(INPUT_VALUE)
    SELECT COL1 FROM [dbo].[fn_Split_String](@INPUT_VALUE,@DELIMITER)


    DROP TABLE IF EXISTS #tmp_Animals
    CREATE TABLE #tmp_Animals
    (
     ANIMAL_ID VARCHAR(128)
    )

    IF @FILTER_TYPE ='Animal_Id_17'
    BEGIN
       INSERT INTO #tmp_Animals
       (
           ANIMAL_ID
       )
       SELECT a.ANIMAL_ID
       FROM #tmp_Inputs t
       INNER JOIN ANIMALS a ON t.INPUT_VALUE = a.ANIMAL_ID
    END 

    SELECT * FROM #tmp_Animals
    ORDER BY ANIMAL_ID

and this is the sp after i converted to DB2

DECLARE @FILTER_TYPE VARCHAR(128);
    SET @FILTER_TYPE = 'Animal_ID_17';
    DECLARE @DELIMITER VARCHAR(3);
    SET @DELIMITER = ',';

    DECLARE GLOBAL TEMPORARY TABLE Input_EMP
    (   
        INPUT_VALUE VARCHAR(128)

    );

    INSERT INTO Input_EMP(INPUT_VALUE)
    SELECT COL1 FROM fn_Split_String @INPUT_VALUE,@DELIMITER;

    DECLARE GLOBAL TEMPORARY TABLE Animals_EMP
    (
        ANIMAL_ID VARCHAR(128)
    );

    IF @FILTER_TYPE ='Animal_Id_17' THEN
        INSERT INTO Animals_EMP
        (
            ANIMAL_ID
        )
        SELECT a.ANIMAL_ID
        FROM Input_EMP a
        JOIN ANIMALS n on a.INPUT_VALUE = n.ANIMAL_ID;
    END IF;

    DECLARE cursor1 CURSOR WITH RETURN for

    SELECT ANIMAL_ID
    FROM Animals_EMP
    ORDER BY ANIMAL_ID;

    OPEN cursor1;

Then i got this error: An unexpected token "" was found following "". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.19.56

Does anyone know how to resolved this error. Thanks

Upvotes: 0

Views: 2131

Answers (1)

mustaccio
mustaccio

Reputation: 18945

Db2 compound SQL statements that comprise bodies of stored procedures require that DECLARE statements precede any executable statements and follow a well defined order, as shown in the linked documentation. In your code they are scattered all over the place, which is what the error message is trying to tell you.

Upvotes: 1

Related Questions