Patrick, November 02, 2007 - 11:57 am UTC
Thanks for the prompt answer
decode and null
A reader, November 04, 2007 - 10:21 am UTC
One exception from the "null rule" is decode
select decode(null,null,1,2) from dual;
concat too
Giacomo Falchetti, November 06, 2007 - 4:44 am UTC
select concat(null, 'x') from dual;
C
-
x
NULL in functions
Duke Ganote, November 06, 2007 - 9:27 am UTC
GREATEST is a "convenience" function. A do-it-yourself CASE statement behaves the same:
select coalesce(to_char(
CASE WHEN a > b and a > c then a
WHEN b > a and b > c then b
WHEN c > a and c > b then c
END
), '<null result>') as result
from ( select 1 as a, null as b, 3 as c from dual )
/
RESULT
-------------
<null result>
Patrick, November 20, 2007 - 3:17 pm UTC
Well with that code won't work if I'm comparing 3 equal values
e.g :
select coalesce(to_char(
CASE WHEN a > b and a > c then a
WHEN b > a and b > c then b
WHEN c > a and c > b then c
END
), '<null result>') as result
from ( select 1 as a, 1 as b, 1 as c from dual )
/
RESULT
----------------------------------------
<null result>
as opposed to using GREATEST :
select greatest(1,1,1)
from dual
/
GREATEST(1,1,1)
----------------------
1
So I don't think GREATEST() is just a convenience function.
November 21, 2007 - 11:18 am UTC
sure it is, just because the original poster's logic wasn't 100% correct doesn't change anything.
ops$tkyte%ORA10GR2> select a,b,c,
2 coalesce(to_char(
3 CASE WHEN a >= b and a >= c then a || ' a'
4 WHEN b >= a and b >= c then b || ' b'
5 WHEN c >= a and c >= b then c || ' c'
6 END
7 ), '<null result>') as result
8 from ( select 1 as a, 1 as b, 1 as c from dual
9 union all select null as a, 1 as b, 1 as c from dual
10 union all select 1 as a, 1 as b, null as c from dual
11 union all select 1 as a, 1 as b, 2 as c from dual
12 )
13 /
A B C RESULT
---------- ---------- ---------- ------------------------------------------
1 1 1 1 a
1 1 <null result>
1 1 <null result>
1 1 2 2 c
RE: a "convenience" function
Duke Ganote, April 11, 2008 - 12:17 pm UTC
THAT's why it's convenient: I'd missed a boundary condition, and -- as Tom says -- why recode what's already developed?
Greatest value in Varchar Column
Snehasish Das, August 26, 2010 - 9:23 am UTC
Hi Tom,
If we do a greatest on two varchar fields having numeric values the results are not constistent. From what I have noticed is that Oracle takes the greatest value if the first charecter is of higher ascii value. i.e to say if we take greatest of 123 and 45 we get 45 as 4 has higher ascii value than 1.
What I wanted to know is that when oracle does a greatest does is add the ascii values of each charecter or it concatenates it while comparing.
i.e to say when we compare 123 and 45 does oracle compare
ascii(1) + ascii(2) + ascii (3) or ascii(1)||ascii(2)||ascii(3).
August 26, 2010 - 1:40 pm UTC
they are consistent.
you are comparing STRINGS, think about how you compare strings.
compare
ABC
DE
which string is larger? DE is because it starts with D. Now, replace A with 1, B with 2 and so on. compare the strings
123
45
and which is bigger - 45 is - because 4 > 1. it is a string. It is consistent. It is done the same way for all strings, you compare byte by byte until you find bytes that are different - the byte that is different with the higher ascii code represents the "bigger" string.
GREATEST(...) NULLS LEAST
Aalbert Torsius, August 31, 2010 - 7:16 am UTC
We could use a way of defining how we'd like NULLS to be handled by GREATEST (and her little brother, LEAST), just like we can do for an ORDER BY.
For instance:
SELECT GREATEST(1, 2, 3, NULL) NULLS LEAST g
FROM dual;
/
G
----------------------
3
SELECT GREATEST(1, 2, 3, NULL) NULLS GREATEST g
FROM dual;
/
G
----------------------
NULL
Is there any chance of seeing that in a future release?
Dates with Greatest function
Anil ML, February 09, 2012 - 9:02 am UTC
One of the more convenient way of using greatest function with dates is NVL with least date.
Example :
SELECT greatest(SYSDATE,NULL,SYSDATE+1) FROM DUAL;
---------------------------------------------------
NULL
The above query returns NULL.
But The below one returns the greatest date
SELECT GREATEST (NVL (SYSDATE, (SYSDATE - 365 * 100)),
NVL (NULL, (SYSDATE - 365 * 100)),
NVL ((SYSDATE + 1), (SYSDATE - 365 * 100))
)
FROM DUAL;
another work around of handling nulls in least /greatest function
Abhijat, March 02, 2012 - 1:13 pm UTC
Hi,
My job was to find out the least of two dates, of course skipping nulls. I tried this.
select decode(date1,null,date2,date2,null,date1, least(date1,date2)) as terminationdate
from table_name;
greatest / coalesce
Leon, March 16, 2012 - 6:56 am UTC
select coalesce(least(a, b), a, b)) from dual will also do the trick.
regarding greatest..
ramu ch, October 11, 2013 - 6:50 am UTC
Hi tom,
I tried the following scenarios with greatest.
Why it is giving the error "Invalid number" when the first value is number and others are strings.
1.select greatest(1,2,3) from dual;
greatest--------3
2.select greatest('ram','Kiran','krishna') from dual;
greatest----ram
3.select greatest('ram',1,3) from dual;
greatest-----ram
4.select greatest(1,'ram','krishna') from dual;
greatest-----Invalid number
October 11, 2013 - 1:30 pm UTC
it, like decode and other "varying length argument list functions", use the first return type as the type for the rest.
think about it - it makes sense doesn't it. greatest can return a number, date or string and knows how to compare numbers correctly:
ops$tkyte%ORA11GR2> select greatest( 1e0, 0100 ) from dual;
GREATEST(1E0,0100)
------------------
100
ops$tkyte%ORA11GR2> select greatest( '1e0', '0100' ) from dual;
GRE
---
1e0
and dates and so on. greatest would be useless against numbers/dates if it just said "everything is a string"
Greatest comparison underlying Technique
Mojo, October 16, 2013 - 1:19 pm UTC
Following up on your reply to Mr.Ram Ch
Query1:
select greatest('#a',00) from dual
O/P :0
Query2:
select greatest('a',9999999) from dual
O/P : a
Why is it that query1 returns 999 while query2 returns #a?
Is`nt character decode to its repective numeric value for comparisons using ASCII(be it oracle)?
November 01, 2013 - 7:31 pm UTC
because that is the way the strings sort????
#a is the "smallest" string.
a is the "largest" string.
00 the number is 0 the number is the string "0", it is greater than #a, less than a and 999999
999999 is the number 999999 is the string '99999' and is less than a, greater than 0 and #a
ops$tkyte%ORA11GR2> create table t ( x varchar2(30) );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values ( '#a' );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 00 );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 'a' );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( 9999999 );
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from t order by 1 DESC;
X
------------------------------
a
9999999
0
#a
you cannot ....
Sokrates, November 01, 2013 - 8:08 pm UTC
compare "greatest" with "order by":
sokrates@11.2 > select * from t order by 1 DESC;
X
------------------------------
a
9999999
0
#a
sokrates@11.2 > select greatest('a',9999999) from dual;
G
-
a
ok, seems to be consistent, but now:
sokrates@11.2 > alter session set nls_sort=german;
Session altered.
sokrates@11.2 > select * from t order by 1 DESC;
X
------------------------------
9999999
0
a
#a
sokrates@11.2 > REM but still:
sokrates@11.2 > select greatest('a',9999999) from dual;
G
-
a
November 01, 2013 - 9:47 pm UTC
ok, true - but in my case - with a simple binary ASCII sort, it showed what I meant.
but true, in general an NLS sort isn't the same as the binary compare.
A reader, November 14, 2014 - 9:19 pm UTC
The following query will return NULL when it's a null.
Select nvl(column,'NULL') from all_employees