Function With Different Returns Db2

Hi I have this function: CREATE or replace FUNCTION Tablereturn (SWITCHER INTEGER) RETURNS TABLE (Test CHAR(9), tester INTEGER ) LANGUAGE SQL CO

Solution 1:

RETURN statement must be the last statement of the function. There is a class of functions called 'pipelined' where you can use 'if then else' logic. Like this:

CREATEOR REPLACE FUNCTION TEST_PIPELINED(P_CHOICE INT)
RETURNSTABLE (R_COL1 VARCHAR(128), R_COL2 VARCHAR(128))
BEGINDECLARESQLSTATECHAR(5);
  DECLARE L_COL1 VARCHAR(128);
  DECLARE L_COL2 VARCHAR(128);
  DECLARE c1 CURSORFOR S1;

  IF P_CHOICE=1THENPREPARE S1 FROM'SELECT COLNAME, COLNO FROM SYSCAT.COLUMNS FETCH FIRST 10 ROWS ONLY';
  ELSEPREPARE S1 FROM'SELECT TABNAME, OWNER   FROM SYSCAT.TABLES  FETCH FIRST 10 ROWS ONLY';
  END IF;

  OPEN c1;
  L1: LOOP
    FETCH c1 INTO L_COL1, L_COL2;
    IF SQLSTATE<>'00000'THEN LEAVE L1; END IF;
    PIPE(L_COL1, L_COL2);
  END LOOP L1;
  CLOSE c1;
  RETURN;
END@

Solution 2:

An alternative to using PIPE would be something simple like this

CREATEOR REPLACE FUNCTION TEST_CHOICE(P_CHOICE INT)
RETURNSTABLE (R_COL1 VARCHAR(128), R_COL2 VARCHAR(128))
RETURNSELECT COLNAME, TYPENAME FROM SYSCAT.COLUMNS WHERE P_CHOICE =1UNIONALLSELECT TABNAME, OWNER     FROM SYSCAT.TABLES WHERE P_CHOICE =2
;