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?
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.
If you use an aggregate function like MAX, NULL values will be be excluded. But then your data needs to come in as rows.
WITH mylist AS (
SELECT NULL AS id FROM DUAL
UNION
SELECT 1 FROM DUAL
UNION
SELECT 2 FROM DUAL
)
SELECT MAX(id)
FROM mylist
Okay, No NVL VALUE or COALESCE
CASE
WHEN term1 IS NULL AND term2 IS NOT NULL AND term3 IS NOT NULL
THEN GREATEST(term2,term3)
WHEN term1 IS NOT NULL AND term2 IS NULL AND term3 IS NOT NULL
THEN GREATEST(term1,term3)
WHEN term1 IS NOT NULL AND term2 IS NOT NULL AND term3 IS NULL
THEN GREATEST(term1,term2)
WHEN term1 IS NOT NULL AND term2 IS NULL AND term3 IS NULL
THEN term1
WHEN term1 IS NULL AND term2 IS NOT NULL AND term3 IS NULL
THEN term2
WHEN term1 IS NULL AND term2 IS NULL AND term3 IS NOT NULL
THEN term3
ELSE GREATEST(term1,term2,term3)
END
But you really should COALESCE each term with a suitably low value.
DECLARE MIN_INT CONSTANT := -2147483648;
GREATEST(COALESCE(term1,MIN_INT),COALESCE(term2,MIN_INT),COALESCE(term3,MIN_INT))
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
The solution is not "coalesce" but rater NVL. If you knew beforehand that the other two values are, for example, positive numbers, then you could do something like greatest(1, NVL(null, -1), 2) - or, more likely, if you have three numeric columns, any one of which could be NULL, and you knew all values must be positive numbers, you could do something like greatest(NVL(col1, -1), NVL(col2, -1), NVL(col3, -1)) - and then, if the result is -1, you would know all three values were NULL.
However, if you allow NULL that probably means you may, sometimes, have values that are not known. Is it not possible that the greatest among your values is in fact one of these unknown values? (That is, isn't NULL the correct answer for greatest() in that case?)
ADDED: You can always avoid coalesce and NVL, for example instead of NVL(col1, -1) you can write case when col1 is null then -1 else col1 end - but WHY???
