Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    2

    Question Unanswered: Pass variable number of params to UDF.

    I am trying to write a scalar UDF which will accept a variable number of parameters (similar to value() and coalesce()). I can consider writing this in either SQL, C, or Java ... the logic is really simple.

    1. Read any number of date parameters (such as several date columns on a single table row).
    2. Iterate through the date parameters and find the highest date value.
    3. Return the highest date value.

    Question 1: What will the UDF signature have to look like (e.g. value() accepts any number of parameters of nearly any data type)?

    Question 2: Is there a way to write this using SQL, such that the parameters can be easily iterated through (e.g. in Java the parameters passed in are simply passed as Array, in C the parameters are passed as va_list).

    Appreciated.

    - brian

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Generally speaking, variable number of parameters for a UDF is annoying.
    But, you can use MAX(GREATEST is a synonym) scalar built-in function for your specific requirement(i.e. Return the highest date value), if you are using DB2 for LUW 9.5.
    Last edited by tonkuma; 05-04-09 at 09:07.

  3. #3
    Join Date
    May 2009
    Posts
    2
    Appreciate the quick response. Unfortunately, we are running DB2 for LUW 8.x. Though perhaps annoying, the need still exists ... unless there is some way to get the 9.5 function to be available in our version 8.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have two ideas. Both are tedious.
    1) If you can limit the maximum number of parameters.
    You can cretate UDFs (maximum number of parameters - 1) times. Like GREATEST(for string or for integer) in
    Sample UDFs for Migration
    Although it showed only datatypes of VARCHAR, INTEGER and DOUBLE,
    you can create UDFs for DATE by changing data types of UDFs using same function bodies.

    2) Use VARCHAR as a parameter.
    Then convert each of your parameters to CHAR, concatenate them in a VARCHAR and pass a concatenated values to the UDF.
    You need to separate(decompose?) each parameters in your UDF.

Posting Permissions

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