From the conspicuous absence of relevant examples, I get the idea that you can't use a SQL "SELECT INTO" statement in a UDF. Is that correct? Anyone?

If so, would that explain the error below?

CREATE FUNCTION Sy_RiskClass(Score INTEGER, AsOf DATE)
RETURNS CHAR(12)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
S1:BEGIN ATOMIC
DECLARE Flag INT;
DECLARE TmpRsk CHAR(12);

IF Score >= 1000
THEN
RETURN 'Not Assigned';
END IF;

SET Flag = 0;
SELECT RISK_CLASS, 1
INTO TmpRsk, Flag
FROM DB2USER.SYMITAR_RISK_CLASS R
WHERE R.EFFECTIVE_DATE =
(SELECT MAX(X.EFFECTIVE_DATE)
FROM DB2USER.SYMITAR_RISK_CLASS X
WHERE X.EFFECTIVE_DATE <= AsOf)
AND R.CUTOFF_SCORE =
(SELECT MAX(Y.CUTOFF_SCORE)
FROM DB2USER.SYMITAR_RISK_CLASS Y
WHERE Y.EFFECTIVE_DATE = R.EFFECTIVE_DATE
AND Y.CUTOFF_SCORE <= Score);

IF Flag = 1 THEN
RETURN TmpRsk;
ELSE
RETURN 'Do Not Load';
END IF;
END S1
@

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "TmpRsk" was found following "SK_CLASS, 1 INTO". Expected tokens may include: "<space>". LINE NUMBER=19. SQLSTATE=42601

hi,

I doubt whether you can write this kind of UDF, at least not for current release of DB2. If you want such feature, you can create external UDF, such as:
CREATE FUNCTION FINDV (CLOB(100K))
RETURNS INTEGER
FENCED
LANGUAGE JAVA
PARAMETER STYLE JAVA
EXTERNAL NAME ’JAVAUDFS.FINDVWL’
NO EXTERNAL ACTION
CALLED ON NULL INPUT
DETERMINISTIC
NO SQL;

And ’JAVAUDFS.FINDVWL' is the application that will do the select into .. jobs.


The above example is copied from SQL reference for DB2/390. I am not sure about DB2 for LUW.

I think you can do this: