oracle greatest null,How to avoid nulls with greatest function?

Alessandro Rossi wrote:

If you have a bag of numbered balls with 3 numbers in it, you know two of the numbers but the 3rd one is unknown to you, which of those numbers is the greatest? Of course the answer is unknown. That sounds perfectly acceptable to me and is exactly what I expect.Your example is not well proposed. I know you interpret null as unknown but in many cases null means missing. So ....

You have a bag with some numbered balls and some unnumbered balls. What could be the greatest number you can spot on the balls?

Can you spot the difference between unknown and nonexistent?Ah, but that's where you're going wrong.

You are providing a NULL value as a parameter. Therefore it is not nonexistent. You are telling the function there is a value, but that the value is unknown. If it were nonexistent then you would have only provided 2 parameters instead of 3.

If you're referring to NULL in programming languages (especially Oracle SQL and PL/SQL), by considering null to always mean "unknown" you will always be on the right track to understanding. Consider it nonexistent and you will confuse yourself. Oracle treats it as "unknown" and that is how I understand it and, in truth, you'd be better off understanding it that way too.

If you don't, just ignore this thread and keep working on your things and have a good life but don't leave such a stupid message! I have a problem and you can't come to me and say that is not a problem, because the problem is there.I'm sorry you have taken offence at it (your problem not mine). I was merely explaining a valid way to consider NULLs within Oracle and other programming languages. I have always considered NULL to mean Unknown and that has always worked. The only time I have ever encountered people experiencing problems understanding something because of NULL is when they do not consider it to mean "unknown".

The analogy of the bag was intended to be helpful.

And why don't you say that it's strange that MAX() and MIN() ignore nulls then? If I would think with your head, I would also say that if I have an unknown value in a set I can't say witch one is the greatest or the least and then they should return null like GREATEST() and LEAST() do giving an "ACCEPTABLE" result . Isn't this right?Part of SQL throughout history has been that aggregate functions typically eliminate nulls from their calculations. It's an ongoing area of controversy and can lead to some unpredictable results... e.g.

The average of 4 rows of values, one containing null...

SQL> ed

Wrote file afiedt.buf

1 with t as (select 150 as x from dual union all

2 select 200 from dual union all

3 select 250 from dual union all

4 select null from dual)

5 --

6 select avg(x)

7* from t

SQL> /

AVG(X)

----------

200... is calculated as the sum of the non-null values divided by the number of non-null rows.

However you may typically expect the null row to be counted, such that the result is the sum of all the values divided by the total number of rows..., effectively treating the null as a zero...

SQL> ed

Wrote file afiedt.buf

1 with t as (select 150 as x from dual union all

2 select 200 from dual union all

3 select 250 from dual union all

4 select 0 from dual)

5 --

6 select avg(x)

7* from t

SQL> /

AVG(X)

----------

150

SQL>... which gives a different result.

That's just one of the things with aggregate functions. Always has been. To be consistent they too should return NULL, and then people would be forced to enter a value on their database to signify a 'default' of some sort i.e. 0 where the data is non-existent.

So, yes, I agree with you that this isn't ideal. As such, there are just the two things to remember...

1) Aggregate functions eliminate nulls

2) Nulls everwhere else should be treated as "unknown".

If you really wanted to get into the nitty gritty of it you could argue that both of these should return null too..

SQL> select 1+null from dual;

1+NULL

----------

SQL> select 'fred'||null from dual;

'FRE

----

fred

SQL>