Undefined number of arguments for SQL User Defined Functions?

Question

In Oracle and MySQL, how can I create a function that takes an indefinite number of parameters so that it can be called GREATEST (value1, value2, ...)?

Comparing two values ​​by a specific standard is pretty simple, but passing the “larger” value to another comparison is something that I don't seem to get work in SQL.

Thanks!

Edit (after Mike's comment below): I'm looking for a solution to compare multiple columns. In a concrete sense, my question is how to implement GREATEST () as UDF. The following code compares three columns.

SELECT CASE WHEN CASE WHEN col_1 < col_2 THEN col_2 ELSE col_1 END < col_3 THEN col_3 ELSE CASE WHEN col_1 < col_2 THEN col_2 ELSE col_1 END END AS greatest FROM figures; 

This does not seem to scale well. It will be much more useful to have a common function that applies the same comparison method over and over to a list of values ​​over and over again.

For SQL, I mean any SQL database product, but I prefer a solution that works in Oracle or MySQL

Answer 1

In Oracle (instances that support univot)

 SELECT MyID, MAX(GreatestVal) FROM figures UNPIVOT ( GreatestVal FOR MyID IN (col_1, col_2, col_3,...) ); 

The Oracle query is not verified because I do not have a convenient instance. More detailed information about univot is located here , and it does the same as without it.

MySQL I'm not sure how to do this in MySQL, but I can investigate, because I have the opportunity (if someone does not beat me to it. ;-))

The following are SQL Server answers:

Doing this in UDF is not very good, because all input parameters will be REQUIRED in each case. If you manage to implement it as a stored procedure, you can specify default values ​​for the input parameters and call them with a dynamic number of columns. In any case, you will have to choose the maximum number of input parameters. Here is an example in UDF on SQL Server:

 SELECT dbo.GREATESTof3(col_1, col_2, col_3) FROM figures; CREATE FUNCTION GREATESTof3(@col_1 sql_variant = null, @col_2 sql_variant = null, @col_3 sql_variant = null) RETURNS sql_variant AS BEGIN DECLARE @GreatestVal sql_variant DECLARE @ColumnVals TABLE (Candidate sql_variant) INSERT INTO @ColumnVals SELECT @col_1 UNION ALL SELECT @col_2 UNION ALL SELECT @col_3 SELECT @GreatestVal = MAX(Candidate) FROM @ColumnVals RETURN @GreatestVal END 

To do this, you will need a new UDF for each variant of the number of input parameters, or create one that can take a larger number, but then in the UDF call you need to either specify some value for each unused parameter (null) or specify a default value.

Alternatives:

This gives the maximum value of three columns from the entire table and it will be easier to have a dynamic number of columns:

 SELECT MAX([Value]) AS Greatest FROM figures UNPIVOT ( [Value] FOR ColumnName IN ([Col_1], [Col_2], [Col_3]) ) AS unpvt 

Assuming you have some kind of rowid or another column that you want in the output so that you can get the largest value from the specified columns for each row, you can do something like this:

 SELECT RowID, MAX([Value]) AS Greatest FROM figures UNPIVOT ( [Value] FOR ColumnName IN ([Col_1], [Col_2], [Col_3]) ) AS unpvt GROUP BY RowID 

Answer 2

Another SQL Server parameter (not sure how well it translates to MySQL / Oracle).

I had to do this with a list of integer identifiers before I put it in a comma-separated list and passed it to the functions to get the highest value:

 CREATE Function [dbo].[GreatestFromList] (@ListOfValues VARCHAR(8000)) RETURNS INT AS BEGIN DECLARE @ListOfValuesTable TABLE (ValueColumn INT) DECLARE @spot1 SMALLINT, @str1 VARCHAR(8000) WHILE @ListOfValues <> '' BEGIN SET @spot1 = CHARINDEX(',', @ListOfValues) IF @spot1>0 BEGIN SET @str1 = LEFT(@ListOfValues, @spot1-1) SET @ListOfValues = RIGHT(@ListOfValues, LEN(@ListOfValues) -@spot1 ) END ELSE BEGIN SET @str1 = @ListOfValues SET @ListOfValues = '' END INSERT INTO @ListOfValuesTable (ValueColumn) VALUES(convert(int, @str1)) END DECLARE @GreatestValue INT SELECT @GreatestValue = SELECT MAX(ValueColumn) FROM @ListOfValuesTable RETURN @GreatestValue END