Home » SQL & PL/SQL » SQL & PL/SQL » case work in 8.1.7 or not ? (Im getting an error) (oracle 8.1.7 ,winxp)
case work in 8.1.7 or not ? (Im getting an error) [message #366356] Sat, 13 December 2008 00:02 Go to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

select ename,
case job when 'clerk' then 1
         when 'salesman' then 2
         else 3 end
from emp;


when im trying to execute the above query i have got error

ORA-00923: from keyword not found where expected...

please help me
Re: case work in 8.1.7 or not ? (Im getting an error) [message #366357 is a reply to message #366356] Sat, 13 December 2008 00:26 Go to previous messageGo to next message
ttparavindh
Messages: 22
Registered: December 2006
Location: Bangalore,India
Junior Member

Its available from Oracle 8i onwards and the same code working for me on 10g.

SQL> select ename,
  2  case job when 'clerk' then 1
  3           when 'salesman' then 2
  4           else 3 end
  5  from emp;

ENAME      CASEJOBWHEN'CLERK'THEN1WHEN'SALESMAN'THEN2ELSE3END
---------- --------------------------------------------------
SMITH                                                       3
ALLEN                                                       3
WARD                                                        3
JONES                                                       3
MARTIN                                                      3
BLAKE                                                       3
CLARK                                                       3
SCOTT                                                       3
KING                                                        3
TURNER                                                      3
ADAMS                                                       3

ENAME      CASEJOBWHEN'CLERK'THEN1WHEN'SALESMAN'THEN2ELSE3END
---------- --------------------------------------------------
JAMES                                                       3
FORD                                                        3
MILLER                                                      3

14 rows selected.


see here:-
http://www.databasejournal.com/features/oracle/article.php/3344871/Oracles-CASE-Expression.htm

http://download.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1002628

Regards,
Aravindh

[Updated on: Sat, 13 December 2008 00:39]

Report message to a moderator

Re: case work in 8.1.7 or not ? (Im getting an error) [message #366359 is a reply to message #366356] Sat, 13 December 2008 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It works but not with this syntax.
SQL> select ename,
  2  case job when 'clerk' then 1
  3           when 'salesman' then 2
  4           else 3 end
  5  from emp;
case job when 'clerk' then 1
         *
ERROR at line 2:
ORA-00923: FROM keyword not found where expected


SQL> select ename,
  2         case
  3           when job='clerk' then 1
  4           when job='salesman' then 2
  5           else 3
  6         end job
  7  from emp;
ENAME             JOB
---------- ----------
SMITH               3
ALLEN               3
WARD                3
JONES               3
MARTIN              3
BLAKE               3
CLARK               3
SCOTT               3
KING                3
TURNER              3
ADAMS               3
JAMES               3
FORD                3
MILLER              3

14 rows selected.

SQL> @v

Version Oracle : 8.1.7.4.1

In addition, it does not work in PL/SQL.

@ttparavindh, please test your answer with the correct version before posting.
Also read the documentation you point yourself, CASE syntax does not mention the one you used.

Regards
Michel
Re: case work in 8.1.7 or not ? (Im getting an error) [message #366373 is a reply to message #366359] Sat, 13 December 2008 02:25 Go to previous message
ttparavindh
Messages: 22
Registered: December 2006
Location: Bangalore,India
Junior Member

I agree with you Sad
Previous Topic: sql problem
Next Topic: Does Selecting From Views Affect Performance?
Goto Forum:
  


Current Time: Sun Dec 11 03:58:58 CST 2016

Total time taken to generate the page: 0.05287 seconds