convert a negative number to 0

Is it possible to convert a negative number to 0?

for example -4 need to be 0 and 3 needs to be 3.

try this

SELET (CASE WHEN MyField <= 0 THEN 0 ELSE MyField END) AS MyNonNegativeField
FROM MyTable|||That will work, except that I have a very lengthy expression for "MyField". I don't want to have to duplicate that expression.|||

If you really don't want to repeat the expression then you could turn your query into a derived table and then SELECT from the derived table, incorporating the CASE logic into that SELECT list.

SELECT t.Column1,
CASE WHEN t.Column2 < 0 THEN 0 ELSE t.Column2 END

FROM (SELECT <My Complex Expression1> AS Column1,

<My Complex Expression2> AS Column2

FROM etc...) t

Chris

|||

Another very simple way is UDF,

Create Function dbo.NegativeToZero(@.Value as int)

returns Int

as

Begin

Return Case When @.Value < 0 Then 0 else @.Value End;

End

Go

Select {Your Columns}, ....., dbo.NegativeToZero(Your Lengthy Expression) as Data

From

YourTable

|||

Just be aware that introducing a UDF into a query often has a negative impact on performance. Better performance is usually obtained by incorporating a UDF's code into the body of the query (although I appreciate that this isn't always possible).

Chris