Home » SQL & PL/SQL » SQL & PL/SQL » CASE and DECODE (Oracle 9i)
CASE and DECODE [message #399558] Thu, 23 April 2009 00:25 Go to next message
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
WHEN sal<2000 THEN 1000
WHEN sal>2000 THEN '2000'
FROM emp;

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

10, 1000,
20, '2000',
'9') "RESAL" FROM emp;

Why it's not raising error?
Re: CASE and DECODE [message #399565 is a reply to message #399558] Thu, 23 April 2009 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63908
Registered: March 2007
Location: Nanterre, France, http://...
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).

Re: CASE and DECODE [message #400076 is a reply to message #399558] Sat, 25 April 2009 15:18 Go to previous message
Barbara Boehmer
Messages: 8608
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.

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

Current Time: Fri Oct 21 04:52:05 CDT 2016

Total time taken to generate the page: 0.17245 seconds