467,209 Members | 1,369 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,209 developers. It's quick & easy.

Creating a simple UDF

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;
Simple enough, right? When I try to run it, I get this error:

SQL0199N The use of the reserved word "FROM" following "" is not
valid.
Expected tokens may include: "ON AFTER <INTEGER>". SQLSTATE=42601

I know I'm missing something minor but can't figure it out

I'm using DB2 OS/390 8.1.0.

Thanks
Amit

Apr 18 '06 #1
  • viewed: 1499
Share:
4 Replies
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
Apr 18 '06 #2
Thanks for your reply, I made some progress. I tried putting
parenthesis around the SELECT statement but now I get this error:

[IBM][DB2] SQL0214N An expression in the ORDER BY clause in the
following position, or starting with "0" in the "RETURN" clause is not
valid. Reason code = "7". SQLSTATE=42822

This is the exact sql:

CREATE FUNCTION MYSCHEMA.GETUSERDEPT(
USR_ID INTEGER)
RETURNS VARCHAR ( 256 )
LANGUAGE SQL
NO EXTERNAL ACTION
RETURN (SELECT DEPT_NM FROM ABC.DEPT WHERE DEPT.USR_ID = USR_ID);

-Amit

Apr 18 '06 #3
Well, we got past the syntax error.. That's good.
Staring at the zOS docs I see:

The expression cannot contain a scalar fullselect.

Bummer...
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 18 '06 #4
D'oh! That sucks.
Thanks anyways!

-Amit

Apr 18 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Carl | last post: by
4 posts views Thread by John Spiegel | last post: by
4 posts views Thread by Phillip Vong | last post: by
11 posts views Thread by Michael7 | last post: by
1 post views Thread by deepapanch@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.