SQL Server 2017 Replacing Negative value without repeating long expression-CodePudding

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?

CodePudding user response：

Consider using a CROSS APPLY. They will allow you to reference aliases and stack calculations

 Select CASE WHEN AVAL < 0 THEN 0 ELSE AVAL END
 From  YourTable A
 Cross Apply ( values (longexpression) )B(AVAL)

CodePudding user response：

Just for some variety...

You could round trip it through FORMAT as this allows positive values, negative values and zeroes to be treated differently.

SELECT CAST(FORMAT({long expression}, '#.########;\0;0') AS DECIMAL(18,8))

I'd just use the APPLY myself though as first port of call (rather than gratuitously calling a known slow function unnecessarily with some completely unwarranted string casting).

CodePudding user response：

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;