Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Unanswered: Case, Subquery, Greater than Comparison

    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
    Last edited by NickJ; 06-20-09 at 06:45.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    SELECT case 
                 when price < 100 then 'cheap'
                 when price < 150 then 'fairly expensive'
                 else 'expensive'
               END
    FROM product;

  3. #3
    Join Date
    Sep 2003
    Posts
    12
    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.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by NickJ
    No, the input expression for the case statement needs to come from a subquery.
    Why?
    Can you show us the whole picture?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •