Case, Subquery, Greater than Comparison

NickJ · #1 (**permalink**) 06-20-09, 05:21

Hi,

Is it possible to have a subquery within a case statement and perform greater than / less than comparisons on the value returned in the subquery?

i.e.

Code:

select case (select price from product)
            when < 100 then 'cheap'
            when between 101 and 150 then 'fairly expensive'
            else 'expensive';

Sorry for the for example, but you get the idea?

Thanks,
nickj

shammat · #2 (**permalink**) 06-20-09, 06:01

Code:

SELECT case 
             when price < 100 then 'cheap'
             when price < 150 then 'fairly expensive'
             else 'expensive'
           END
FROM product;

NickJ · #3 (**permalink**) 06-20-09, 07:09

No, the input expression for the case statement needs to come from a subquery.

So the following would work...

Code:

SELECT case 
             when (select price from product) < 100 then 'cheap'
             when (select price from product) < 150 then 'fairly expensive'
             else 'expensive'
           END

... but wanted to do this withouth having repeat the subquery each time.