Search code examples
db2db2-400db2-luw

what is wrong with db2 procedure?

The SP gets compiled but when executing doesn't display values. I wanted to show paid date and service date along with their column name.

CREATE OR REPLACE
PROCEDURE DATETYPE ()

RESULT SETS 1
LANGUAGE SQL
SPECIFIC DATE
BEGIN
DECLARE C1 CURSOR FOR SELECT
   1 AS Value,
   'Paid Dates Only' AS LABEL
FROM    sysibm.sysdummy1
UNION ALL SELECT
   2 AS Value,
   'Service Date' AS LABEL
FROM    sysibm.sysdummy1;


OPEN C1;

END;
Solution

  • You have to tell which cursor you want as result set, even if there is only one

    DECLARE C1 CURSOR WITH RETURN FOR SELECT...