| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
07-12-11, 17:11
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
What's Wrong ?
|
|
I want create SQL UDF function (V9 z/os):
Code:
CREATE FUNCTION RandInt(Core Int)
RETURNS integer
contains SQL
NO EXTERNAL ACTION
not DETERMINISTIC
RETURN
Select Integer(RandInt.Core * Rand()) FROM sysibm.sysdummy1
;
Instead of function I have a message together with sqlcode = -104:
Quote:
SQL0104N An unexpected token "INTEGER" was found following "". Expected tokens may include: "SOURCE PARAMETER STOP INHERIT RETURNS CALLED ALLOW CONTAINS". SQLSTATE=42601
State:42601,Native:-104,Origin:[IBM][CLI Driver][DB2]
|
Can somebody explain me where I have made mistake ? 
Thanks, Lenny
|
Last edited by Lenny77; 07-12-11 at 17:14.
|
07-12-11, 17:17
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Although I don't know the relationship with the message,
I think that contains SQL should be READS SQL DATA.
|
Last edited by tonkuma; 07-12-11 at 17:20.
|
07-12-11, 17:17
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
|
|
You are returning a table, not an integer.
|
|
07-12-11, 17:20
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Updated
Quote:
Originally Posted by tonkuma
Although I don't know the relationship with the message SQL0214N,
I think that contains SQL should be READS SQL DATA.
|
This message was the wrong one. I changed it.
Please, read again.
|
|
07-12-11, 17:23
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
...returns table (...)...
Quote:
Originally Posted by n_i
You are returning a table, not an integer.
|
Where you find the word "TABLE" ?
Nor a Table, nor a Row -- scalar function.
|
Last edited by Lenny77; 07-12-11 at 17:28.
|
07-12-11, 17:28
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
There is not a puzzle, but a real production problem !
Thanks
|
|
07-12-11, 17:29
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
RETURN Integer(RandInt.Core * Rand())
would work with CONTAINS SQL.
|
|
07-12-11, 17:33
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
Originally Posted by tonkuma
RETURN Integer(RandInt.Core * Rand())
would work with CONTAINS SQL.
|
That's right. I know that.
But I want to use Select statement on Return clause. What I show to you just the simplest form of the return/select.
Thanks
|
|
07-12-11, 17:42
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
DB2 9 - DB2 SQL - RETURN statement
No select-statement(nor subselect) for returned value.
Quote:
RETURN statement
The RETURN statement is used to return from the routine.
For SQL functions, it returns the result of the function. For an SQL procedure, it optionally returns an integer status value.
Syntax
Code:
>>-+--------+--RETURN--+------------+--------------------------><
'-label:-' +-expression-+
'-NULL-------'
|
|
|
07-12-11, 17:53
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
07-12-11, 17:56
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
Originally Posted by tonkuma
|
In this case I have had a message which you saw already on the very first screen:
Quote:
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 = "6". SQLSTATE=42822
State:42822,Native:-214,Origin:[IBM][CLI Driver][DB2]
|
Thanks
|
|
07-12-11, 18:03
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Please see my previous posts.
Quote:
|
I think that contains SQL should be READS SQL DATA.
|
|
|
07-13-11, 10:54
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
DB2 Bug
Quote:
Originally Posted by tonkuma
Please see my previous posts.
|
I begun with this with the same result:
Quote:
SQL0104N An unexpected token "INTEGER" was found following "". Expected tokens may include: "SOURCE PARAMETER STOP INHERIT RETURNS CALLED ALLOW CONTAINS". SQLSTATE=42601
State:42601,Native:-104,Origin:[IBM][CLI Driver][DB2]
|
Maybe we have DB2 bug ?
Thanks
|
|
07-13-11, 11:20
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
I thought that
you got SQL0104N by
CONTAINS SQL
and
RETURN SELECT ...
and you got SQL0214N by
CONTAINS SQL
and
RETURN (SELECT ...)
So, my proposal was
READS SQL DATA
and
RETURN (SELECT ...)
|
|
07-13-11, 14:20
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
sqlcode = -214
Quote:
Originally Posted by tonkuma
I thought that
you got SQL0104N by
CONTAINS SQL
and
RETURN SELECT ...
and you got SQL0214N by
CONTAINS SQL
and
RETURN (SELECT ...)
So, my proposal was
READS SQL DATA
and
RETURN (SELECT ...)
|
Code:
CREATE FUNCTION RandInt(Core Int)
RETURNS integer
reads SQL data
NO EXTERNAL ACTION
not DETERMINISTIC
RETURN
(Select Integer(RandInt.Core * Rand()) FROM sysibm.sysdummy1)
;
But still no success:
Quote:
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 = "6". SQLSTATE=42822
State:42822,Native:-214,Origin:[IBM][CLI Driver][DB2]
|
Thanks
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
What's Wrong ?
