I wanted to use function LEAST in my procedure to find the smallest value. The problem is that some of the values might have been NULLs so if I do
select least(NULL,0,1) from dual
The answer I get is NULL, which is probably correct by is not something I am expecting to return. I would like to get the least real non zero value. Any help greatly appreciated.
I doubt that's actually your query. Maybe you're doing something more like this?
select least(some_column) from dual
If so, change it to this:
select least(some_column) from dual where some_column is not null
Or, if you're doing something more like this, where you can't just use where to filter the set,
select least(expr1,expr2,expr3) from dual
do this:
select least(coalesce(expr1, 12345), coalesce(expr2, 12345), coalesce(expr3, 12345)) from dual
Where 12345 is a value big enough that it would only be chosen if all other expressions are NULL.