Results 1 to 3 of 3

Thread: SQL function

  1. #1
    Join Date
    Nov 2007
    Posts
    27

    Unanswered: SQL function

    hi i have a function with this definition.
    if i want to pass a null value for num2
    how do i do that? is it possible to pass null values in the parameters???
    my db is v8.2fp5-solaris


    CREATE FUNCTION udb.db2_values(name1 VARCHAR(40),
    num1 INTEGER,
    num2 INTEGER)
    RETURNS VARCHAR(45)
    SPECIFIC DSTAGE.SQL18010925763584990
    LANGUAGE SQL
    NOT DETERMINISTIC
    EXTERNAL ACTION
    READS SQL DATA
    NULL CALL
    INHERIT SPECIAL REGISTERS
    BEGIN ATOMIC
    (
    SQL
    BODy
    ....
    ....
    ....
    ....

    )
    i have tried like select udb.db2_values(xyz,123,null) from sysibm.sysdummy1;
    this gave me an error.
    so can any one please help me out with this.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rajaryan4545
    i have tried like select udb.db2_values(xyz,123,null) from sysibm.sysdummy1;
    Code:
    select udb.db2_values(xyz,123,cast(null as integer))
    DB2 needs to know what is the data type of your null in order to determine which function you are calling.

    Code:
    CREATE FUNCTION udb.db2_values(name1 VARCHAR(40),
    num1 INTEGER,
    num2 INTEGER)
    and
    Code:
    CREATE FUNCTION udb.db2_values(name1 VARCHAR(40),
    num1 INTEGER,
    num2 BIGINT)
    would be two different functions.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Exactly.

    The main thing to remember here is that NULL is untyped per definition. Also, even if you have only one function and there is supposedly no doubt which function DB2 should choose, it will still throw the error. The reason is simply that an overloaded function (like n_i's BIGINT version) may be added in the future. That could have an impact on existing packages and dynamic SQL, so DB2 kindly asks you to be a bit more specific an what you want instead of randomly choosing one function (which may be the wrong one).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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