I am getting following Error message while creating IBM db2 procedure

Question

What is wrong with the following code?

PROCEDURE DATETYPE DYNAMIC RESULT SETS 1 LANGUAGE SQL
BEGIN
DECLARE @DateType TABLE
    ( LABEL CHAR(30) ,
    Value VARCHAR(1) );

DECLARE C CURSOR WITH RETURN FOR;

INSERT
    INTO
        @DateType
    VALUES ('Paid Dates Only',
    'P') INSERT
        INTO
            @DateType
        VALUES('Incurred Dates with Paid',
        'S') SELECT
            *
        FROM
            @DateType;

OPEN C;
END

I am using db2 and gets the following error:

SQL Error [42601]: An unexpected token "( LABEL CHAR(30) , Value VARCHAR(1) )" was found following " ". Expected tokens may include: "E @DateType TABLE ".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.21.29

nfgl nfgl · Accepted Answer · 2020-09-22T15:58:05

wichever DB2 it is (db2-400 or db2-luw) DECLARE xx TABLE is not DB2 SQL PL syntax

Btw if I understand it well your code can be replaced with a view

CREATE OR REPLACE VIEW DATETYPE (LABEL, VALUE) as (
    VALUES 
    ('Paid Dates Only', 'P'),
    ('Incurred Dates with Paid', 'S')
)

edit:since it has to be a prodecure, then you can use

   CREATE OR REPLACE PROCEDURE DATETYPE ()
     RESULT SETS 1
     LANGUAGE SQL
   BEGIN
       DECLARE DATAS CURSOR WITH RETURN FOR 
            VALUES 
            ('Paid Dates Only', 'P'),
            ('Incurred Dates with Paid', 'S');
       OPEN DATAS;
   END

I am getting following Error message while creating IBM db2 procedure

1 Answers