Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: DB2 Use SELECT INTO in UDFs?

    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

  2. #2
    Join Date
    May 2003
    Posts
    113
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you can do this:
    Code:
    	SET (TmpRsk, Flag) = SELECT RISK_CLASS, 1
    	FROM	DB2USER.SYMITAR_RISK_CLASS R
    	WHERE	...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •