Results 1 to 8 of 8
  1. #1
    Join Date
    May 2002
    Posts
    2

    Unanswered: Scalar function with SELECT statement

    I'm new to DB2 - all my experience is in Oracle.
    I am trying to write a function that will return the scalar result of a SELECT statement:

    Code:
    CREATE FUNCTION myResult(inID INTEGER)
    RETURNS CHAR(6)
    LANGUAGE SQL READS SQL DATA
    NO EXTERNAL ACTION NOT DETERMINISTIC
    RETURN SELECT name FROM myTable WHERE id = inID
    I have tried every variation of this I can think of, but can't get it to work. The only examples I can find of user defined functions that use SELECT statements actually return a table, and I do not want to return a table.

    ideas? sources with examples?

    Thanks!

  2. #2
    Join Date
    May 2002
    Location
    Pune, India
    Posts
    23
    Hi maljsl,
    I wonder what your error was coming out to be? Were you able to create the function or not? I am listing the SQL I used to create the function. and it does work!!

    db2 CREATE FUNCTION myResult3(inID int) RETURNS CHAR(20) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION NOT DETERMINISTIC RETURN SELECT distinct deptname FROM org WHERE deptnumb = inID

    Ensure that the function result is only one row(Ensured by distinct option here ). And after that the select statement like:

    Select deptnumb,myResult(deptnumb) from org

    returns the expected resultset.

    Regards
    Sumeet

  3. #3
    Join Date
    May 2002
    Location
    Pune, India
    Posts
    23
    Sorry abt the mistake, but I guess you got the import of the idea!!
    Read as:
    Select deptnumb,myResult3(deptnumb) from org
    instead of
    Select deptnumb,myResult(deptnumb) from org

    Regards
    Sumeet

  4. #4
    Join Date
    May 2002
    Posts
    2
    Hi Sumeet,
    Thanks for the response.

    I tried this:

    Code:
    CREATE FUNCTION myResult3(inID int) 
    RETURNS VARCHAR(50) 
    LANGUAGE SQL 
    READS SQL DATA 
    NO EXTERNAL ACTION 
    NOT DETERMINISTIC 
    RETURN SELECT DISTINCT clnt_ds FROM clnt WHERE clnt_id = inID ;
    Gives this error message:
    Code:
    SQLCODE : -104
    SQL0104N  An unexpected token "CLNT_DS" was found following "".  Expected 
    tokens may include:  "ON <INTEGER> ".  SQLSTATE=42601
    
    SQLSTATE 42601: A character, token, or clause is invalid or missing.
    Are you using DB2 on OS/390?

  5. #5
    Join Date
    May 2002
    Location
    Pune, India
    Posts
    23
    Are you using DB2 on OS/390?
    No I am not but if u are then try going to this resource on the net.

    http://publib.boulder.ibm.com/cgi-bi...20010417105742


    Hope this helps
    Sumeet

    PS: Include the platform/db2 version information in your posting to get accurate responses.

  6. #6
    Join Date
    May 2003
    Posts
    1

    What IS the resolution

    I am having the EXACT same problem attempting to create a UDF in DB2, but noone ever answered this question w/ a solution!

    I could use some help here too!

  7. #7
    Join Date
    May 2003
    Posts
    113

    Re: What IS the resolution

    DB2 390 may have different syntax with other platform(such as w2k).

    for your stmt above, you tried to create a UDF sql scalar function, an example from 390 sql ref book:
    CREATE FUNCTION TAN (X DOUBLE)
    RETURNS DOUBLE
    LANGUAGE SQL
    CONTAINS SQL
    NO EXTERNAL ACTION
    DETERMINISTIC
    RETURN SIN(X)/COS(X);

    At the last line, it's called RETURN-stmt, which can not be a sql-select stmt, you can only put expression in it.

    For your purpose, it looks like a external table UDF for 390. I don't have an example for it now. Anyway, I am not sure whether 390 db2 can do it


    Originally posted by csheley
    I am having the EXACT same problem attempting to create a UDF in DB2, but noone ever answered this question w/ a solution!

    I could use some help here too!

  8. #8
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Re: Scalar function with SELECT statement

    hi,
    I test your function and is ok. now, what is your db2 version? and where use this function?
    for exaple, I use the function in a select statements

    b2 => select name,myResult(dept) from staffg fetch first 10 row only

    NAME 2
    ------------------ -----------------------------
    Sanders Mid Atlantic
    Pernal Mid Atlantic
    Marenghi South Atlantic
    O'Brien South Atlantic
    Hanes New England
    Quigley South Atlantic
    Rothman New England
    James Mid Atlantic
    Koonitz Great Lakes
    Plotz Great Lakes

    regards
    abel

Posting Permissions

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