sql - Avoiding NULL return value with GREATEST function

Question

How can I avoid a null return value when invoking the greatest function with a null parameter. Example:

select greatest(1,null,2) from dual

The above returns null, but I want 2.

I don't want to use nvl and also not something like:

coalesce(greatest(term1,term2,term3), greatest(term1,term2), term1).

Is there is any other way of avoiding the null return value?

Answer 1

Oracle Setup:

CREATE FUNCTION greatest_ignore_null(
  vals SYS.ODCINUMBERLIST
) RETURN NUMBER
IS
  output NUMBER := NULL;
BEGIN
  FOR i IN 1 .. vals.COUNT LOOP
    IF vals(i) IS NOT NULL AND ( output IS NULL OR vals(i) > output ) THEN
      output := vals(i);
    END IF;
  END LOOP;
  RETURN output;
END;
/

Query:

SELECT greatest_ignore_null(
         SYS.ODCINUMBERLIST( 1, NULL, 3, NULL, 2 )
       ) AS largest
FROM DUAL;

Output:

LARGEST
-------
      3