Case Function Options

[thiyagusham]

Hi to everyone;


I want to perform some operation with case statement. But I am confusing with ora 00932 error. My question is what data type should I use while performing case function?

SQL> select * from samp;

NAME EMPID SALARY DEPT
---
sony 10680 8200 sap
bala 10708 4300 .net
sam 10600 9000 oracle
chris 10655 5500 java
rose 10487 8700 oracle
sona 10687 5500 java
maya 10700 8600 sap

SQL> select name,empid,salary,dept, case dept
when 'oracle' then salary*1.8
when 'java' then salary*1.2
when 'sap' then salary*1.3
else salary
end "RESULT" from samp;
else salary
*
ERROR at line 5:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

SQL> desc samp;
Name Null? Type
---
NAME VARCHAR2(15)
EMPID VARCHAR2(15)
SALARY VARCHAR2(15)
DEPT VARCHAR2(10)


My big question is

different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.


// just I am trying to perform basic operation. why oracle didn't support?

[burleson]

>> My big question is different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.

Huh? This is not a cogent question.

See here, how to use CASE:

http://dba-oracle.com/t_case_sql_clause.htm