CODEWITHSUNDEEP
sqloracle-database
Avi
Avi

Reputation: 1845

Oracle differences using Quotes in case statement and insert

I encountered this scenario where the insert based on '' on number column works fine while the case statement gives different error. "inconsistent datatypes: expected %s got %s"

I have gone through some of the explanation given here (ORA-00932 inconsistent datatypes expected char got number ) and Oracle SQL CASE WHEN ORA-00932: inconsistent datatypes: expected CHAR got NUMBER 00932. 00000 - "inconsistent datatypes: expected %s got %s" and I understand that I need to use it without quotes but I wanted to understand the behavior of case statement. SQL server does not behave like this, but why only oracle. Is this oracle default behavior with case statement.

If I create and insert values, I can do it without any error. 

create table testtbl (name varchar2(50), id number(10,2)); 

insert into testtbl values ('abc', '123'); 
insert into testtbl values ('test', '200');

When I try to update the column using case statement I am getting this error. I can use to_number or remove the quotes to avoid this error but wanted to understand why it happens with quotes when the column given in the else statement is number itself. Doesn't implicit conversion exist in Oracle? 

update testtbl 
set id = case when name = 'abc' then '5000' else id end;

Upvotes: 1

Views: 104

Answers (1)

stefan
stefan

Reputation: 2252

Doesn't implicit conversion exist in Oracle?

It does. See documentation eg here.

Is this oracle default behavior with case statement.

Yes. From the documentation:

For both simple and searched CASE expressions, all of the return_exprs must either have the same datatype (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric datatype.

Upvotes: 1

Related Questions