I'm new to DB2 - all my experience is in Oracle.
I am trying to write a function that will return the scalar result of a SELECT statement:
CREATE FUNCTION myResult(inID INTEGER)
LANGUAGE SQL READS SQL DATA
NO EXTERNAL ACTION NOT DETERMINISTIC
RETURN SELECT name FROM myTable WHERE id = inID
I have tried every variation of this I can think of, but can't get it to work. The only examples I can find of user defined functions that use SELECT statements actually return a table, and I do not want to return a table.
CREATE FUNCTION myResult3(inID int)
READS SQL DATA
NO EXTERNAL ACTION
RETURN SELECT DISTINCT clnt_ds FROM clnt WHERE clnt_id = inID ;
Gives this error message:
SQLCODE : -104
SQL0104N An unexpected token "CLNT_DS" was found following "". Expected
tokens may include: "ON <INTEGER> ". SQLSTATE=42601
SQLSTATE 42601: A character, token, or clause is invalid or missing.
DB2 390 may have different syntax with other platform(such as w2k).
for your stmt above, you tried to create a UDF sql scalar function, an example from 390 sql ref book:
CREATE FUNCTION TAN (X DOUBLE)
NO EXTERNAL ACTION
At the last line, it's called RETURN-stmt, which can not be a sql-select stmt, you can only put expression in it.
For your purpose, it looks like a external table UDF for 390. I don't have an example for it now. Anyway, I am not sure whether 390 db2 can do it
Originally posted by csheley
I am having the EXACT same problem attempting to create a UDF in DB2, but noone ever answered this question w/ a solution!
I test your function and is ok. now, what is your db2 version? and where use this function?
for exaple, I use the function in a select statements
b2 => select name,myResult(dept) from staffg fetch first 10 row only
Sanders Mid Atlantic
Pernal Mid Atlantic
Marenghi South Atlantic
O'Brien South Atlantic
Hanes New England
Quigley South Atlantic
Rothman New England
James Mid Atlantic
Koonitz Great Lakes
Plotz Great Lakes