OraFAQ Forum: SQL & PL/SQL » CASE and DECODE

Home » SQL & PL/SQL » SQL & PL/SQL » CASE and DECODE (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CASE and DECODE [message #399558]

Thu, 23 April 2009 00:25

ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad

Senior Member

SQL> SELECT sal, CASE
WHEN sal<2000 THEN 1000
WHEN sal>2000 THEN '2000'
ELSE 0
END
FROM emp;

WHEN sal>2000 THEN '2000'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

SQL>SELECT DECODE (deptno,
10, 1000,
20, '2000',
'9') "RESAL" FROM emp;

Why it's not raising error?

Report message to a moderator

Re: CASE and DECODE [message #399565 is a reply to message #399558]

Thu, 23 April 2009 00:48

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A useful link for you: SQL Reference.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

Report message to a moderator

Re: CASE and DECODE [message #400076 is a reply to message #399558]

Sat, 25 April 2009 15:18

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

CASE has the restriction that all of the corresponding expressions must be of the same datatype, as stated in the following section of the online documentation. Apparently DECODE does not have this limitations and can do an implicit conversion.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/expressions5a.htm#1033394

Report message to a moderator

Previous Topic:	Select table based on condition
Next Topic:	Alias in ORDER BY

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 17:01:12 CST 2024