Log in  ::  Register  ::  Not logged in

## Turn negative numbers into 0

 Author Message Chrissy321 SSCrazy Eights Group: General Forum Members Points: 8802 Visits: 5166 Hello all,I have a select statement something like this.SELECT....COALESCE(here I take the first non-null from 4 conditions, the conditions are fairy complicated) AS MyComplexCalcFROM...My additional requirement is that if the complicated calc returns a negative number replace that with 0.The following should work but I don't like repeating the calc from a code maintenance or performance perspective. Is there something cleaner where I do not have to repeat the calc?SELECT....CASE COALESCE (here I take the first non-null from 4 conditions, the conditions are fairy complicated) <0 THEN 0ELSECOALESCE(here I take the first non-null from 4 conditions, the conditions are fairy complicated)END AS MyComplexCalcFROM... The Dixie Flatline One Orange Chip Group: General Forum Members Points: 28587 Visits: 6919 Use a cte, like so:`;with cte (newvalue) as (select COALESCE etc FROM sometable)select case when newvalue < 0 then 0 else newvalue end as newvalue from cte` __________________________________________________Against stupidity the gods themselves contend in vain. -- Friedrich Schiller Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills Paul White SSC Guru Group: General Forum Members Points: 79812 Visits: 11400 You can avoid performing the complex calculation more than once:`DECLARE @A INTEGER = NULL, @B INTEGER = -351, @C INTEGER = 45, @D INTEGER = 999;SELECT Calculation.result * ((SIGN(Calculation.result) + 1) / 2)FROM ( SELECT TOP (1) result = COALESCE(@A, @B, @C, @D) ORDER BY result) AS Calculation;` Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi The Dixie Flatline One Orange Chip Group: General Forum Members Points: 28587 Visits: 6919 And also...`-- To make result = 0 when @test < 0declare @test int -7select (ABS(@test)+@test)/2 as result` __________________________________________________Against stupidity the gods themselves contend in vain. -- Friedrich Schiller Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills Paul White SSC Guru Group: General Forum Members Points: 79812 Visits: 11400 CELKO (7/18/2011)SIGN (1 + SIGN(x)) * x but it means using x twice. Hide it in a CTE and use the twice.CTEs generally don't help - the expression is evaluated twice.I included the TOP trick as the only way I know to force the query processor to evaluate just once. Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi Paul White SSC Guru Group: General Forum Members Points: 79812 Visits: 11400 The Dixie Flatline (7/18/2011)And also...`select (ABS(@test)+@test)/2 as result`Neat! Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi mister.magoo One Orange Chip Group: General Forum Members Points: 26514 Visits: 7939 SQLkiwi (7/18/2011)CTEs generally don't help - the expression is evaluated twice.I included the TOP trick as the only way I know to force the query processor to evaluate just once.Here is another way then (learned from Stefan G) to force the calculation to only happen once:`;with calc(result) as(select COALESCE(@A, @B, @C, @D)union allselect nullfrom calc where 1=0)select calc.result * ((sign(calc.result) + 1) / 2)from calc `The recursive part of the CTE returns no extra data, but coerces sql into storing the results (it will plop the results in tempdb once there are enough I believe) rather than re-evaluating them - it's a nice little trick that can sometimes help. MM`select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);`Forum Etiquette: How to post Reporting Services problemsForum Etiquette: How to post data/code on a forum to get the best help - by Jeff ModenHow to Post Performance Problems - by Gail Shaw SQLRNNR SSC Guru Group: General Forum Members Points: 144547 Visits: 18651 SQLkiwi (7/18/2011)The Dixie Flatline (7/18/2011)And also...`select (ABS(@test)+@test)/2 as result`Neat!Agreed - good thinking there Bob Jason...AKA CirqueDeSQLeil_______________________________________________I have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail ShawLearn Extended Events