#1
  1. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22

    IF statement within a SELECT??? HELP?


    I need to do an if statement within a select... I have basically this...

    SELECT (If a price >200, enter value 'Expensive' into the column... else enter value 'Value') AS PriceType
    FROM ...

    Can someone convert that English to SQL? I've tried a few things, to no avail, and would prefer not to create a view just to do this...

    Thanks!
  2. #2
  3. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    Anyone, please??? I'm dyin' here...
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,202
    Rep Power
    4279
    Code:
    select case when price > 200
                then 'Expensive' 
                else 'Value'      end as PriceType
      from ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    SELECT A.Name, A.Gender, SA.SalePrice AS Price, (SELECT CASE SA.SalePrice > 200 THEN 'Expensive' ELSE 'Value' END) AS PriceType

    FROM Animal A, SaleAnimal SA
    WHERE A.AnimalID = SA.AnimalID AND
    A.Category = @BCategory AND
    A.Breed = @breed
    ORDER BY A.Name ASC


    Is this right? Still not working properly...
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,202
    Rep Power
    4279
    Code:
    select A.Name
         , A.Gender
         , SA.SalePrice as Price
         , case when SA.SalePrice > 200 
                then 'Expensive' 
                else 'Value' end AS PriceType
      from Animal A
    inner
      join SaleAnimal SA
        on A.AnimalID = SA.AnimalID 
     where A.Category = @BCategory 
       and A.Breed = @breed
    order 
        by A.Name asc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Business Analyst
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2004
    Location
    The 'Ville
    Posts
    1,087
    Rep Power
    22
    Got it, was missing one keyword... appreciate the help.

IMN logo majestic logo threadwatch logo seochat tools logo