views:

1233

answers:

3

I have some negative values coming back from a query. I would like them to just be zero. How do I write a condition in my sql query that returns zero if the value is below a certain value.

sol:

CASE WHEN  CONVERT(float,dt.FQI53X02_101)  <  1.3  THEN 0 ELSE CONVERT(float,dt.FQI53X02_101) END AS  FQI53X02_101
+7  A: 

You dont use If-Then-Else in the actual query (you can use them but thats something else)...

What you use is a Case statement... Try this

Select
    Case When [Value] < 0 Then 0 Else [Value] End
From 
    Example
Chalkey
my variation of your sugestion is: "select dt.* , Case FQI53X02_101 When < 1.3 Then 0 Else FQI53X02_101 End as hhh from" I get an error back on the less-than operator...not suer why
Brad
Okay I got it "CASE WHEN CONVERT(float,dt.FQI53X02_101) < 1.3 THEN 0 ELSE CONVERT(float,dt.FQI53X02_101) END AS FQI53X02_101"
Brad
+5  A: 

If you want it as part of your query, wrap the return inside a CASE statement. Example from MSDN is below

SELECT     'Price Category' = 

        CASE 

            WHEN price IS NULL THEN 'Not yet priced'

            WHEN price < 10 THEN 'Very Reasonable Title'

            WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'

            ELSE 'Expensive book!'

        END,

    CAST(title AS varchar(20)) AS 'Shortened Title'

FROM titles

ORDER BY price
Cody C
+2  A: 
( ABS(Value) + Value ) / 2

edit - this doesn't work now the question has changed

Tom Haigh
Nice one. +1 (rubbish to get 15 chars)
Burkhard