Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Closed TopicStart new topic
> Case Function
thiyagusham
post Apr 3 2012, 06:11 AM
Post #1


Advanced Member
***

Group: Members
Posts: 78
Joined: 16-March 12
From: Chennai from India
Member No.: 46,939



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?
Go to the top of the page
 
+Quote Post
burleson
post Apr 3 2012, 07:01 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,812
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd December 2014 - 09:57 PM