Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Unanswered: DB2 UDF (Oracle, MSSQL)

    I must be looking in all the wrong places whether IBM manuals, online Help or on the Net....

    I create a scalar UDF such as:

    create function one()
    returns integer
    return 1

    Now, in MS SQL or Oracle, I can then enter a SQL statement such as:

    SELECT one(); or SELECT one() from DUAL; and I get the number "1" returned as one row but I can't seem to get the equivalent in DB2.

    If I try my UDF - or a system scalar function, for example:

    select tan(1.5) from syscat.functions

    I get the answer hundreds of times (it's probably not the right way then...)

    Can anybody tell me the equivalent in DB2? The documentation always refers to some user defined table somewhere. Thanks...

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: DB2 UDF (Oracle, MSSQL)

    Originally posted by KontinMonet
    I must be looking in all the wrong places whether IBM manuals, online Help or on the Net....

    I create a scalar UDF such as:

    create function one()
    returns integer
    return 1

    Now, in MS SQL or Oracle, I can then enter a SQL statement such as:

    SELECT one(); or SELECT one() from DUAL; and I get the number "1" returned as one row but I can't seem to get the equivalent in DB2.

    If I try my UDF - or a system scalar function, for example:

    select tan(1.5) from syscat.functions

    I get the answer hundreds of times (it's probably not the right way then...)

    Can anybody tell me the equivalent in DB2? The documentation always refers to some user defined table somewhere. Thanks...
    When you execute

    select tan(1.5) from syscat.functions

    you, obviously, receive the value of tangent for each row in syscat.functions, which may well be in the hundreds.

    There is an equivalent of Oracle's DUAL table in DB2; its name is "SYSIBM.SYSDUMMY1". It only contains one row; as a result,

    SELECT TAN(1.5) FROM SYSIBM.SYSDUMMY1

    will return only one row. Another way to get the same result would be

    VALUES(TAN(1.5))
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your problem is that you need a table with only one row so that your result set has only one row. IBM provides just such a table: sysibm.sysdummy1

    try:

    select tan(1.5) from sysibm.sysdummy1

    Andy

Posting Permissions

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