Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    33

    Unanswered: SQL Case Statement Question

    If you look at the below code I was wondering if I can substitue the 'Expensive Item!!!' text with a value from the actual table.

    For example:

    Instead of the exisiting Line: Which just produces the text EXPENSIVE ITEM
    ELSE 'EXPENSIVE ITEM!!!'

    Replace it with: Which would pull the product price from a table
    ELSE product.price

    but I can't get this to work.

    USE NORTHWINDSELECT

    'COMMENT' =
    CASE
    WHEN UNITPRICE IS NULL THEN 'NO PRICE DEFINED'
    WHEN UNITPRICE < 20 THEN 'LOW PRICE'
    WHEN UNITPRICE > 20 AND UNITPRICE < 30 THEN 'REASONABLE PRICE'
    ELSE 'EXPENSIVE ITEM!!!'
    END,
    PRODUCTNAME,
    UNITPRICE
    FROM PRODUCTS

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    USE NORTHWIND;
    
    SELECT CASE
              WHEN UNITPRICE IS NULL THEN 'NO PRICE DEFINED'
              WHEN UNITPRICE < 20 THEN 'LOW PRICE'
              WHEN UNITPRICE > 20 AND UNITPRICE < 30 THEN 'REASONABLE PRICE'
              ELSE 'EXPENSIVE ITEM (' + CAST(UNITPRICE as VARCHAR(20)) + ')!!!'
           END as PriceIndicator,
           PRODUCTNAME,
           UNITPRICE
    FROM PRODUCTS
    Replace it with: Which would pull the product price from a table
    ELSE product.price
    Isn't UNITPRICE good enough? Is "product" another table than "PRODUCTS" or is it just a typo? If they are different tables, you will have to join them before you can use product.price instead of UNITPRICE in the ELSE branch of the CASE.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so 19 is low, 21 is reasonable, but exactly 20 is expensive, eh?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2010
    Posts
    33

    Solved:

    Thanks WIM you provided me the information I needed to solve my problem.

Posting Permissions

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