6 Replies Latest reply: Jan 28, 2013 10:15 PM by 987536 RSS

    Comparing Null with Null in Decode

    987536
      Hi all,

      I am karthik,

      update emp
      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

      thanks,
      karthik m
        • 1. Re: Comparing Null with Null in Decode
          Peter vd Zwan
          Hi,

          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.

          >


          PS.
          Welcome to the forum.

          Regards,

          Peter
          • 2. Re: Comparing Null with Null in Decode
            ranit B
            Welcome to the Forum!!!

            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;
            gives
            EQUAL          EQUAL
            NOT Equal     UNknown
            NOT Equal     UNknown
            EQUAL          UNknown
            Ranit B.

            Edited by: ranit B on Jan 28, 2013 6:51 PM
            -- code added
            • 3. Re: Comparing Null with Null in Decode
              Karthick_Arp
              984533 wrote:
              Hi all,

              I am karthik,

              update emp
              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

              thanks,
              karthik m
              DECODE has the capability to compare NULL.
              SQL> select decode(null, null, 'NULL', 'NOT NULL') from dual;
               
              DECO
              ----
              NULL
              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+"_

              http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm#SQLRF00631
              • 4. Re: Comparing Null with Null in Decode
                987536
                Hi peter,

                Thanks a lot
                • 5. Re: Comparing Null with Null in Decode
                  987536
                  Hi Ranit B,

                  Thank you very much
                  • 6. Re: Comparing Null with Null in Decode
                    987536
                    Hi karthick ARP

                    thanks