If the arguments are coming from some table, you can do a scalar sub-query, like this:
SELECT MAX (column_value) FROM TABLE ( sys.odcinumberlist ( -999 , 1 , 999 , NULL ) ) ;
Output from the last query:
SELECT ename , job , ( SELECT MIN (column_value) FROM TABLE ( sys.odcivarchar2list ( ename , job , NULL , 'FOO' ) ) ) AS least_string FROM scott.emp ;
NAME JOB LEAST_STRING ---------- --------- --------------- SMITH CLERK CLERK ALLEN SALESMAN ALLEN WARD SALESMAN FOO JONES MANAGER FOO MARTIN SALESMAN FOO BLAKE MANAGER BLAKE CLARK MANAGER CLARK SCOTT ANALYST ANALYST KING PRESIDENT FOO TURNER SALESMAN FOO ADAMS CLERK ADAMS JAMES CLERK CLERK FORD ANALYST ANALYST MILLER CLERK CLERK
Alessandro Rossi wrote:Ah, but that's where you're going wrong.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?
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".
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.
... is calculated as the sum of the non-null values divided by the number of non-null rows.
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
... which gives a different result.
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>
SQL> select 1+null from dual; 1+NULL ---------- SQL> select 'fred'||null from dual; 'FRE ---- fred SQL>
Alessandro Rossi wrote:You're right if you have just some date fields. That would be a quite typical case. In this case you always go with the NVL logic or with some special CASE handling. I had the impression that you have many more fields then just "some".
It's not denormalized data it's just a table with some date fields that has to be joined with another one on the greatest criteria.
I don't think this could be so unusual.
substr(greatest(x||exp1, ... x||expn),2)Edited by: Sven W. on Sep 11, 2009 3:38 PM
But using nullif(nvl) method in this case would fail without noticing any error while using the subtr one you're only limited by the fact that you can't use strings longer than 3999 bytes.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss' 2 / Session altered. SQL> SQL> select nullif(greatest(nvl(null,date'0001-01-01'),nvl(date'0001-01-01',date'0001-01-01')),date'0001-01-01') 2 from dual 3 / NULLIF(GREATEST(NVL ------------------- SQL> SQL> select substr(greatest('x'||null,'x'||date'0001-01-01'),2) 2 from dual 3 / SUBSTR(GREATEST('X' ------------------- 0001-01-01 00:00:00 SQL>
horten69 wrote:Don't worry about it your needs are not like mine. We're all different from each other and it's not that bad. I wouldn't be that happy to meet just people like me I like comparing my opinions with others and of wouldn't be different from mine I wouldn't need it.
I cannot see a need of GREATESTNNV or LEASTNNV as you can easily mask your parameters with the NVL function and choose an approriate value to replace the nulls...
And even if we agreed in the point that it would be nice to have those functions, it would not help you to solve your problem, as this functions are not around yet nor in 11g nor in release 2 and i doubt they will in oracle 12...Nothing more right than that but probably this thread could inspire someone there to introduce them. As someone said there is a little competitor who already did it, so there could be one time when Oracle would do it too.