#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    3
    Rep Power
    0

    SELECT (a field if not zero, else another field), field, field FROM tablename ?


    Normally I would do a select like:
    SELECT doors, windows, tyres from CAR

    But in this case I have poor input data where maybe "doors" is set to 0 on CAR, but set properly in another table. Then I could do:

    SELECT m.doors, c.windows, c.tyres FROM car INNER JOIN model m ON c.model_id = m.model_id

    What I actually want to do though, is say "I want c.doors if it's not zero, otherwise use m.doors". But I'm not sure how to make a statement like that or if a stored procedure is required.

    Can you help?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391

    Smile


    Code:
    SELECT case when c.doors = 0 then 
                     m.doors else c.doors end as doors,
               c.windows, 
               c.tyres 
      FROM car INNER JOIN model m 
         ON c.model_id = m.model_id
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    you can simplify the CASE expression like this --

    COALESCE(NULLIF(c.doors,0),m.doors) AS doors


    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    114
    Rep Power
    12
    i created a UDF for this as its a common issue. i use it like the isnull() function. helps alot for divide by 0 errors.

    CREATE function [dbo].[IsZero](@value DECIMAL(24,12),@elseval DECIMAL (24,12) )
    returns DECIMAL(24,2)
    as begin

    if @value=0
    BEGIN
    return @elseval
    END
    if @value != 0
    BEGIN
    return @value
    END

    return @value
    end

IMN logo majestic logo threadwatch logo seochat tools logo