2 Replies Latest reply: Oct 3, 2013 5:56 AM by KVB RSS

    Understanding the CASE statement

    KVB

      I got confused over this CASE

       

      1) SELECT CASE WHEN 111='111' THEN 'YES' ELSE 'NO' END FROM DUAL    --YES

       

      2)SELECT CASE WHEN COALESCE(111,111)='111' THEN 'YES' ELSE 'NO' END FROM DUAL  --YES

       

      3)SELECT CASE WHEN COALESCE(111,'111')='111' THEN 'YES' ELSE 'NO' END FROM DUAL

      ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

       

      What exactly the things happening between 2&3.Does'nt it convert implicitly as case 2 did because in case 3 the first value is anyways not null as case2..This leads to the confusion.

      I read the Oracle documentation and there is a table of converting implicit data types.It also indicates that can be converted.Still there is some confusion raised.Could anyone twist that confusion ?

        • 1. Re: Understanding the CASE statement
          BluShadow

          No, the coalesce function requires all inputs to be of the same datatype as they are arguments to the function.

          Implicit datatype conversion typically happens when things are compared with operators (as you are doing in example 1 and 2.

          There's not many functions/procedures of that nature that will take mixed datatypes and convert them for you.

           

          So, it's nothing to do with the CASE statements, it's to do with the COALESCE function.  It's overloaded for particular datatypes, but it does require all the arguments to be of the same datatype.

          • 2. Re: Understanding the CASE statement
            KVB

            Thank You!