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

Answer 2

One solution is to use a SELECT query with a common table expression to put the values into something resembling a table, and then use the SQL MAX function in a manner similar to the following:

DECLARE
  n1    NUMBER := 1;
  n2    NUMBER := NULL;
  n3    NUMBER := 3;
  nMin  NUMBER := -99999999999999999999999999999999999999e125;
  nMax  NUMBER;
BEGIN
  WITH CTE AS (SELECT n1 AS N FROM DUAL
               UNION ALL
               SELECT n2 AS N FROM DUAL
               UNION ALL
               SELECT n3 AS N FROM DUAL)
  SELECT MAX(COALESCE(n, nMin))
    INTO nMax
    FROM CTE;

  DBMS_OUTPUT.PUT_LINE('nMax=' || nMax);
END;

The above uses COALESCE, which is the ANSI equivalent of NVL. If you don't want to use either NVL or COALESCE you can use a CASE-expression instead:

  WITH CTE AS (SELECT n1 AS N FROM DUAL
               UNION ALL
               SELECT n2 AS N FROM DUAL
               UNION ALL
               SELECT n3 AS N FROM DUAL)
  SELECT MAX(CASE
               WHEN N IS NULL THEN nMin
               ELSE N
             END)
    INTO nMax
    FROM CTE;

Best of luck.