Amit wrote:
I'm trying to create a simple UDF like so:
CREATE FUNCTION MYSCHEMA.GETUSERDEPT(USR_ID INTEGER)
RETURNS VARCHAR ( 256 )
LANGUAGE SQL
NO EXTERNAL ACTION
RETURN SELECT DEPT_NAME FROM ABC.DEPT WHERE DEPT.USR_ID = USR_ID;
You return a scalar subquery. Try wrapping it into braces:
RETURN (SELECT DEPT_NAME FROM ABC.DEPT WHERE DEPT.USR_ID = USR_ID);
DB2 for LUW is more relaxed here because it supports table functions.
DB2 zOS does not so:
RETURN <expr>
<expr> = [.... | ( <subselect> )]
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab