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 ?
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.