Search code examples
sql-servert-sqlcasesql-server-2017

SQL Server 2017 Replacing Negative value without repeating long expression

I have a long expression which may return a positive, negative or zero decimal value. I would like to do this:

SELECT CASE WHEN {long expression} < 0 THEN 0 ELSE {long expression} END

But I don't want to repeat the long expression. I would like something like ISNULL, such as

SELECT ISNEGATIVE({long expression}, 0)

But that doesn't seem to be a thing. Obviously GREATER would work but it's 2017.

I'm pretty sure I'm hosed, but was hoping for a miracle. Anyone?

Solution

  • A CTE could be a good fit here. Something like:

    with cte as (
  select *,
     longExpression = «long expression definition here»
  from yourTable
)
select «other stuff»,
   CASE WHEN longExpression < 0 THEN 0 ELSE longExpression END
from cte;