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 ?