Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Unanswered: Help with Db2 User Defined Function

    Hi,

    I need help with a user defined function.
    I am getting an error with the below function

    CREATE OR REPLACE FUNCTION GETDSSRANGE (AMOUNT DECIMAL(18,2))
    RETURNS SMALLINT
    LANGUAGE SQL
    READS SQL DATA
    NO EXTERNAL ACTION
    DETERMINISTIC
    BEGIN ATOMIC
    DECLARE RNG_CD SMALLINT;
    SELECT CAST(DSS_RNG_CD AS SMALLINT) FROM INTDM.TBINTL_RNG_CD_XREF WHERE GETDSSRANGE.AMOUNT BETWEEN DSS_LWLM_NO AND DSS_UPLM_NO AND DSS_RNG_TY_NM ='ASSET' ;
    SET RNG_CD=DSS_RNG_CD ;
    RETURN RNG_CD;
    END

    Error I am getting :
    SQL0104N An unexpected token "smallint" was found following "TOMIC declare rng_cd". Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=1. SQLSTATE=42601

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) Did you specified statement termination character?

    2) Even if you could create the function with no error,
    I guessed that you can't return the result from the select statement.
    So, please try...
    Code:
    CREATE OR REPLACE FUNCTION
     GETDSSRANGE (AMOUNT DECIMAL(18,2))
    RETURNS SMALLINT 
    LANGUAGE SQL
    READS SQL DATA
    NO EXTERNAL ACTION
    DETERMINISTIC
    RETURN
    SELECT CAST(DSS_RNG_CD AS SMALLINT)
     FROM  INTDM.TBINTL_RNG_CD_XREF
     WHERE GETDSSRANGE.AMOUNT BETWEEN DSS_LWLM_NO AND DSS_UPLM_NO
       AND DSS_RNG_TY_NM ='ASSET'
    ;

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    I need to return a scalar. Above code will return a table. And i am a bit confused how to use the statement termination.
    My code has semicolons. So should i be using db2 -td -f code.sql o execute it?
    Last edited by abhegade; 08-02-11 at 01:11.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Above code will return a table
    No.
    If the select statement in your code returns only one row,
    the return statement in my example will return a scalar value.

    If the select statement in your code returns multiple rows,
    you should decide a row which should be returned.
    One way may be to add FETCH FIRST 1 ROW ONLY (and optional ORDER BY clause), like...
    Code:
    CREATE OR REPLACE FUNCTION
    ...
    ...
    RETURN
    SELECT CAST(DSS_RNG_CD AS SMALLINT)
     FROM  INTDM.TBINTL_RNG_CD_XREF
     WHERE GETDSSRANGE.AMOUNT BETWEEN DSS_LWLM_NO AND DSS_UPLM_NO
       AND DSS_RNG_TY_NM ='ASSET'
     ORDER BY ...
     FETCH FIRST 1 ROW ONLY
    ;

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example of SQL scalar function having only a return statement in the body.

    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE OR REPLACE FUNCTION
     test_get_wordept( in_edlevel INT )
    RETURNS CHAR(3)
    LANGUAGE SQL
    READS SQL DATA
    NO EXTERNAL ACTION
    DETERMINISTIC
    RETURN
    SELECT workdept
     FROM  employee
     GROUP BY
           workdept 
     HAVING
           in_edlevel
           BETWEEN MIN(edlevel) AND MAX(edlevel)
     ORDER BY
           workdept
     FETCH FIRST 1 ROW ONLY
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES test_get_wordept(15);
    ------------------------------------------------------------------------------
    
    1  
    ---
    A00
    
      1 record(s) selected.

Posting Permissions

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