Pran Pran - 6 months ago 11
SQL Question

Avoiding NULL return value with GREATEST function

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?

MT0 MT0
Answer

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