Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: What's Wrong ?

  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down Unanswered: 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:

    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 18:14.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 18:20.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You are returning a table, not an integer.

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Post Updated

    Quote Originally Posted by tonkuma View Post
    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.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Cool ...returns table (...)...

    Quote Originally Posted by n_i View Post
    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 18:28.

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    There is not a puzzle, but a real production problem !

    Thanks

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    RETURN Integer(RandInt.Core * Rand())
    would work with CONTAINS SQL.

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by tonkuma View Post
    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

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 9 - DB2 SQL - RETURN statement

    No select-statement(nor subselect) for returned value.

    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-------'

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    scalar-fullselect was included in Expressins.

    DB2 9 - DB2 SQL - Expressions

    DB2 9 - DB2 SQL - Scalar-fullselect

    So, try
    RETURN (Select Integer(RandInt.Core * Rand()) FROM sysibm.sysdummy1)

    And
    I think that contains SQL should be READS SQL DATA.

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow

    Quote Originally Posted by tonkuma View Post
    scalar-fullselect was included in Expressins.

    DB2 9 - DB2 SQL - Expressions

    DB2 9 - DB2 SQL - Scalar-fullselect

    So, try
    RETURN (Select Integer(RandInt.Core * Rand()) FROM sysibm.sysdummy1)
    In this case I have had a message which you saw already on the very first screen:
    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

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see my previous posts.
    I think that contains SQL should be READS SQL DATA.

  13. #13
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question DB2 Bug

    Quote Originally Posted by tonkuma View Post
    Please see my previous posts.
    I begun with this with the same result:
    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

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 ...)

  15. #15
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down sqlcode = -214

    Quote Originally Posted by tonkuma View Post
    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:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •