473,324 Members | 1,646 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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
4 1634
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: mr_burns | last post by:
hi, i have recently used a system (oscommerce store) where you can backup your database. what happens is that when you click 'backup database', it creates an SQL string, stores it in a text file...
5
by: Carl | last post by:
Hi, I am studying C++ right now. I am interested in creating my own mathematical software. Do you have any suggestions on the libraries or std libraries that I can use to creat very simple graphics...
4
by: John Spiegel | last post by:
Hi all, How would you go about creating a .lnk file programmatically? I can't seem to find an appropriate class. TIA, John
16
by: pukivruki | last post by:
hi, I wish to create a temporary table who's name is dynamic based on the argument. ALTER PROCEDURE . @PID1 VARCHAR(50), @PID2 VARCHAR(50), @TICKET VARCHAR(20)
4
by: Phillip Vong | last post by:
I'm using VS2005 and creating a simple test ASPX page in VB. I have a simple FORMVIEW1 with a label "YTDLabel" databound to a SQL DB. The DB datatype is set to decimal (18,2) and the value of...
0
by: PracticalApps | last post by:
I looked to find a canned solution to create a Word document in my application and just couldn't find anything that just gets to the point. I would think, and I may be making too strong of an...
0
by: stopohno | last post by:
I have written 2 C# project's in the same solution: FeeEngine and DocumentObject. I wrote a simple vbscript to test creating the objects since I am compiling the output into dll's using COM Interop....
3
by: jport | last post by:
Hi Everyone, Am attempting to use MS Access 2000 and ArcGIS to build a comprehensive Land Referencing system, that is orientated to the new and inexperienced user, while also being versatile...
11
by: Michael7 | last post by:
Hi everyone, Me again, been a while since I've been here, but I'm back again needing help with a simple thing. I've searched for help on creating a footer, but all the help I've found, was...
1
by: deepapanch | last post by:
Hi All: I am new to C#. Have created a simple outlook add-in, which adds a command bar in Outlook. On click of the button, I would like to load the a simple dialog with a Text Box and a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.