January 13th, 2009, 11:34 PM
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?
January 14th, 2009, 04:41 AM
SELECT case when c.doors = 0 then
m.doors else c.doors end as doors,
FROM car INNER JOIN model m
ON c.model_id = m.model_id
January 14th, 2009, 05:54 AM
you can simplify the CASE expression like this --
COALESCE(NULLIF(c.doors,0),m.doors) AS doors
January 21st, 2009, 11:52 AM
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) )
if @value != 0