case statment in oracle

[rajakamaraj]

Hi I have a question in using case statement in Oracle.Like is it possible can we use queries in when clause and then clause in case statement. Like
select AMOUNT1,
(CASE WHEN (select amount1 from emp where amount LIKE '%J%') THEN (select CONCAT('-',TRUNC(TO_NUMB ER(REPLACE('000 0116J','J','1') )/100,2)) from dual)
ELSE 'AMOUNT' END) Amount from emp;

Help me out!!!!!!!!

[chandu031]

Hi,

You can use a SELECT in the THEN clause of a case statement but you can't use it in the WHEN clause. The WHEN clause expects a condition which is evaluated to either TRUE or FALSE. There has to be a comparison of two parameters.

[debasisdas]

The expression u pass to WHEN should evalute to some BOOLEAN value
ans since in oracle SQL does not support BOOLEAN ,no query will evalute to BOOLEAN values
so u cant pass queries here.
but u can do that at THEN part ,because that is what u want ot display when the condition satisfies.