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

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.

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.

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.