Lookup table values directly in UDF

[c***@cslucas.com.sg]

How do you retrieve a single values from a select statement and assign it to a variable in a UDF.

Example (not a real business case)

CREATE FUNCTION MYFUNCTION(id INT)
RETURNS INT
LANGUAGE SQL

BEGIN ATOMIC
DECLARE v_age INT;
SELECT S.AGE FROM STAFF S WHERE S.STAFFID = id;
<how to assign S.AGE to v_age so that the value can be returned.>
RETURN v_age;
END


The SELECT statement will always return a single row.

Currently I construct the Select into a SQL string and then pass it to a to a helper UDF that then calls a store procedure. The store procedure then prepare the statement, Open, Fetch and return the required value. I think the overhead doing it like this is quite large. I now got the following error SQL0101N The statement is too long or too complex. SQLSTATE=54001. Not sure if its related.

Thanks!

JJ

[Andreas Kannegiesser]

Post by c***@cslucas.com.sg
How do you retrieve a single values from a select
statement and assign it to a variable in a UDF.
Example (not a real business case)
CREATE FUNCTION MYFUNCTION(id INT)
RETURNS INT
LANGUAGE SQL
BEGIN ATOMIC
DECLARE v_age INT;
SELECT S.AGE FROM STAFF S WHERE S.STAFFID = id;
<how to assign S.AGE to v_age so that the value can

SET v_age = ( SELECT S.AGE FROM STAFF S WHERE S.STAFFID = id );

or

SELECT S.AGE INTO v_age FROM STAFF S WHERE S.STAFFID = id;

Post by c***@cslucas.com.sg
be returned.>
RETURN v_age;
END
The SELECT statement will always return a single row.
Currently I construct the Select into a SQL string
and then pass it to a to a helper UDF that then calls
a store procedure. The store procedure then prepare
the statement, Open, Fetch and return the required
value. I think the overhead doing it like this is
quite large. I now got the following error SQL0101N
The statement is too long or too complex.
SQLSTATE=54001. Not sure if its related.
Thanks!
JJ

Good luck
aka.

[Ian]

Post by c***@cslucas.com.sg
How do you retrieve a single values from a select statement and assign it to a variable in a UDF.
Example (not a real business case)
CREATE FUNCTION MYFUNCTION(id INT)
RETURNS INT
LANGUAGE SQL
BEGIN ATOMIC
DECLARE v_age INT;
SELECT S.AGE FROM STAFF S WHERE S.STAFFID = id;
<how to assign S.AGE to v_age so that the value can be returned.>
RETURN v_age;
END

How about simply,

CREATE FUNCTION MYFUNCTION(id INT)
RETURNS INT
LANGUAGE SQL
return select s.age from staff s where s.staffid = id;
END


No need to set up variables, etc. for something this simple.