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
> Data Type Consistency CASE and Decode
thiyagusham
post Apr 6 2012, 03:54 PM
Post #1


Advanced Member
***

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



Data Type Consistency CASE and Decode

CASE expects data type consistency, DECODE not expecting.

Obviously both functions handling data types are different

let it be

SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual;

RESULT
---
2


SQL> select case 2
2 when 1 then 1
3 when 2 then 2
4 else 3
5 end "RESULT" from dual;

RESULT
---
2


SQL> select case 2
2 when 1 then 1
3 when '2' then 2
4 else 3
5 end "RESULT" from dual;


ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
May i know cause of error here????

I mean " every time case exp checking data type consistency"

my thought is

I am trying to get same output but different methods.

Yes, clearly states this is one of the Oracle bug!


I want to know how oracle handles here ???? i mean 3rd query.
Purely i am testing this function with dual(dummy) table...
obviously, no possibilities for different data type.
next one i am not sure about
Oracle compares int variables to int variables, char variables to char variables,

I think so .... that's why oracle throws error. am i right ??
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

see this query

SQL> select case 2
2 when 1 then 1
3 when '2' then 2
4 else 3
5 end "RESULT" from dual;

// clearly i am stating what's error on 3rd line ?????
hope you can understand...
Even if I refer reference books some concepts are blind to understand.

Hope this helps, to know what's my expectation
Go to the top of the page
 
+Quote Post
aussie_dba
post Apr 6 2012, 04:25 PM
Post #2


Advanced Member
***

Group: Members
Posts: 618
Joined: 28-August 04
Member No.: 495



"Even if I refer reference books some concepts are blind to understand."

OK, so you are not intelligent enough to learn Oracle.

Have you tried something you are not blind to understand, like becoming an English grammar expert?

"Hope this helps, to know what's my expectation"

Your expectation is to get somebody to do your homework for you.

That is cheating.
Go to the top of the page
 
+Quote Post
burleson
post Apr 6 2012, 04:44 PM
Post #3


Advanced Member
***

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



Hi,

>> ORA-00932: May i know cause of error here????


http://www.dba-oracle.com/sf_ora_00932_inc..._got_string.htm

Cause: One of the following:

- An attempt was made to perform an operation on incompatible datatypes. For example, adding a character field to a date field (dates may only be added to numeric fields) or concatenating a character field with a long field.

- An attempt was made to perform an operation on a database object (such as a table or view) that is not intended for normal use. For example, system tables cannot be modified by a user. Note that on rare occasions this error occurs because a misspelled object name matched a restricted object's name.

- An attempt was made to use an undocumented view.


--------------------
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: 25th October 2014 - 02:39 PM