ORA-00907: missing right parenthesis

[sugumar1985]

[code=oracle]
select * from test where sal in ( case sal
when (sal < 5000) and (sal > 2000) then 2000
else 3000
end)

SQL> /
when (sal<5000 and sal>2000) then 2000
*
ERROR at line 2:
ORA-00907: missing right parenthesis

[/code]

if i executing this stmt , i m getting an error message

[maddurivenkys]

Hi,

Try belwo one it will work for you.

[code=oracle]
select * from emp where sal=(case when sal>2000 and sal<5000 then 2000 else 3000 end)
[/code]

Let know if you face any issues.

[amitpatel66]

All,

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

[amitpatel66]

[code=oracle]
select * from test where sal in ( case sal
when (sal < 5000) and (sal > 2000) then 2000
else 3000
end)

SQL> /
when (sal<5000 and sal>2000) then 2000
*
ERROR at line 2:
ORA-00907: missing right parenthesis

[/code]

if i executing this stmt , i m getting an error message

The error is because you have used column name "sal" twice in your CASE, one immediately after "CASE" keyword and then again with WHEN sal <.

Try this:
[code=oracle]

select * from test where sal in (case when (sal < 5000) and (sal > 2000) then 2000
else 3000
end)

[/code]

[amitpatel66]

Hi,

Please remember to provide a meaningful Title for any threads started (see the FAQ entry Use a Good Thread Title).

This helps to ensure that other members, and also the general public, will have a better chance of finding answers to any similar questions.

MODERATOR

[QVeen72]

Hi,

You cant write a "Case" statement in Where Condition when you are using a "Select" Query....

Try This :
[code=oracle]
Select T.* , ( case when (sal < 5000) and (sal > 2000) then 2000
else 3000 end) from test T
[/code]

Regards
Veena