SQL Server 2017 Replacing Negative value without repeating long expression

Question

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?

Do any of the answers to [Is there a Max function in SQL Server that takes two values like Math.Max in .NET?](https://stackoverflow.com/q/124417/1115360) help? — Andrew Morton, Nov 01 '22 at 17:52

John Cappelletti · Answer 1 · 2022-11-01T18:02:15.227

1

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)

edited Nov 01 '22 at 18:02

answered Nov 01 '22 at 17:44

John Cappelletti

79,615
7
44
66

score 1 · Answer 2 · answered Nov 01 '22 at 18:28

1

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).

answered Nov 01 '22 at 18:28

Martin Smith

438,706
87
741
845

I ALWAYS forget about format option ! – John Cappelletti Nov 01 '22 at 18:32
@JohnCappelletti - In this case probably best forgotten about anyway – Martin Smith Nov 01 '22 at 18:33
1

I support format() provided it is used sparingly. :) It is just another tool in the belt. – John Cappelletti Nov 01 '22 at 18:34
1

yeah - if it is being applied to a number of rows that are fit for human consumption (i.e. a small number) then it is plenty fast enough. Generally that is when you would expect `FORMAT` to be used. The case in this question has no such guarantees though. – Martin Smith Nov 01 '22 at 18:37

score 0 · Accepted Answer · answered Nov 01 '22 at 19:52

0

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;

answered Nov 01 '22 at 19:52

Ben Thul

31,080
4
45
68

Probably the most elegant workaround. – Jim 88888 Dec 15 '22 at 22:36

SQL Server 2017 Replacing Negative value without repeating long expression

3 Answers3