| Prajnan |
Posted: Apr 11, 2006 07:47:42 AM |
Total Post: 7
Joined: Jan, 2006
|
Hi,
In case of 'countries' table in the HR Schema of Oracle 9i, the query:
SELECT country_name,
CASE region_id
WHEN 1 THEN 'Europe'
WHEN 2 THEN 'America'
WHEN 3 THEN 'Asia'
ELSE 'Other'
END
FROM countries;
gives the following error:
WHEN 1 THEN 'Europe'
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected.
1) What is the problem with this statement?
2) What is the correct way to write simple CASE statements?
Regards,
Prajnan.
|
|
|
 Subhranil |
| Posted: Apr 12, 2006 07:33:53 AM |  |
|
Total Post: 28
Joined: Jul, 2005
|
Hi Pajnan!
Your query is working fine in sql release version 10.2.0.1.0
If you are using some early version of sql plus
use this query to get the desired result.
SELECT country_name,
CASE WHEN region_id=1 THEN 'Europe'
WHEN region_id=2 THEN 'America'
WHEN region_id=3 THEN 'Asia'
ELSE 'Other'
END "Region Id"
FROM countries.
Regards
Subhranil Chakraborty
|
|
|
|
|
| Prajnan |
| Posted: Apr 12, 2006 11:22:29 PM | |
|
Total Post: 7
Joined: Jan, 2006
|
Hi Subhranil,
I am using Oracle 9.2.0.1.0. The searched CASE statement is working fine in this but the simple CASE is not.
Regards,
Prajnan.
|
|
|
|
|
| Subhranil |
| Posted: Apr 13, 2006 01:26:24 AM | |
|
Total Post: 28
Joined: Jul, 2005
|
Hi Pajnan!
I think simple case statement will not work in your PL/SQL version.
It's working fine in 10.2.0.1.0
Here is the output...
SQL> SELECT country_name,
2 CASE region_id
3 WHEN 1 THEN 'Europe'
4 WHEN 2 THEN 'America'
5 WHEN 3 THEN 'Asia'
6 ELSE 'Other'
7 END
8 FROM countries;
COUNTRY_NAME CASEREG
---------------------------------------- -------
Argentina America
Australia Asia
Belgium Europe
Brazil America
Canada America
Switzerland Europe
China Asia
Germany Europe
Denmark Europe
Egypt Other
France Europe
COUNTRY_NAME CASEREG
---------------------------------------- -------
HongKong Asia
Israel Other
India Asia
Italy Europe
Japan Asia
Kuwait Other
Mexico America
Nigeria Other
Netherlands Europe
Singapore Asia
United Kingdom Europe
COUNTRY_NAME CASEREG
---------------------------------------- -------
United States of America America
Zambia Other
Zimbabwe Other
25 rows selected.
SQL> spool off
Subhranil
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
