This content has been marked as final. Show 6 replies
Hi,1 person found this helpful
This is working as desinged.
As stated in the manual the decode function treats null's equal.
DECODE ( expr , search , result, default)
In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then
Oracle returns the result of the first search that is also null.
Welcome to the forum.
Welcome to the Forum!!!1 person found this helpful
NULL is handled very differently in DECODE and in CASE WHEN.
Please check this small workout...
with xx as( select 1 a, 1 b from dual UNION ALL select 1 a, NULL b from dual UNION ALL select NULL a, 1 b from dual UNION ALL select NULL a, NULL b from dual ) select decode(a,b,'EQUAL','NOT Equal'), case when a=b then 'EQUAL' when a != b then 'NOT Equal' else 'UNknown' end case from xx;
EQUAL EQUAL NOT Equal UNknown NOT Equal UNknown EQUAL UNknown
Edited by: ranit B on Jan 28, 2013 6:51 PM
-- code added
1 person found this helpful
984533 wrote:DECODE has the capability to compare NULL.
I am karthik,
set ename = decode((select ename from emp where empno = 769),
(select ename from emp where empno = 772),
(select '1' from dual),'F_B')where empno = 7839;
actually both the select statement inside the decode written null.
here both null are equated and updated 1 with emp table. (null=null not possible)
'NULL cannot be equated with anything '
why and what happen here?
Please reply to me
This is documented. The document says _"+In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null+"_
SQL> select decode(null, null, 'NULL', 'NOT NULL') from dual; DECO ---- NULL
Thanks a lot
Hi Ranit B,
Thank you very much
Hi karthick ARP