Help with Db2 User Defined Function

abhegade · #1 (**permalink**) 08-01-11, 23:17

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

tonkuma · #2 (**permalink**) 08-01-11, 23:55

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'
;

abhegade · #3 (**permalink**) 08-01-11, 23:57

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?

tonkuma · #4 (**permalink**) 08-02-11, 00:24

Quote:

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
;

tonkuma · #5 (**permalink**) 08-02-11, 00:28

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech