Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Turn negative numbers into 0 Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, July 18, 2011 4:59 PM
 SSChasing Mays Group: General Forum Members Last Login: Yesterday @ 11:59 AM Points: 638, Visits: 3,582
 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...
Post #1143810
 Posted Monday, July 18, 2011 5:22 PM
 Hall of Fame Group: General Forum Members Last Login: Friday, November 29, 2013 10:10 AM Points: 3,896, Visits: 5,800
 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? -- Stephen Stills
Post #1143813
 Posted Monday, July 18, 2011 6:33 PM
 SSChampion Group: General Forum Members Last Login: Today @ 3:11 AM Points: 11,052, Visits: 10,819
 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 WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #1143827
 Posted Monday, July 18, 2011 8:25 PM
 SSCommitted Group: General Forum Members Last Login: Tuesday, January 15, 2013 11:11 AM Points: 1,945, Visits: 2,782
 SIGN (1 + SIGN(x)) * x but it means using x twice. Hide it in a CTE and use the twice. Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #1143834
 Posted Monday, July 18, 2011 8:44 PM
 Hall of Fame Group: General Forum Members Last Login: Friday, November 29, 2013 10:10 AM Points: 3,896, Visits: 5,800
 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? -- Stephen Stills
Post #1143838
 Posted Monday, July 18, 2011 9:32 PM
 SSChampion Group: General Forum Members Last Login: Today @ 3:11 AM Points: 11,052, Visits: 10,819
 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 WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #1143850
 Posted Monday, July 18, 2011 9:34 PM
 SSChampion Group: General Forum Members Last Login: Today @ 3:11 AM Points: 11,052, Visits: 10,819
 The Dixie Flatline (7/18/2011)And also...`select (ABS(@test)+@test)/2 as result`Neat! Paul WhiteSQL Server MVPSQLblog.com@SQL_Kiwi
Post #1143851
 Posted Tuesday, July 19, 2011 5:04 PM
 SSCommitted Group: General Forum Members Last Login: Tuesday, January 15, 2013 11:11 AM Points: 1,945, Visits: 2,782
 SQLkiwi (7/18/2011)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.WITH FourThingies ( e1, e2, e3, e4)AS(SELECT (??) AS e1, (??) AS e2, (??) AS e3, (??) AS e4 FROM .. WHERE..)SELECT SIGN (1 + SIGN(e1)) * e1 , SIGN (1 + SIGN(e2)) * e2 SIGN (1 + SIGN(e3)) * e3, SIGN (1 + SIGN(e4)) * e4 FROM FourThingies;I know it re-computes a table expressions in T-SQLI am hoping that the optimizer will "do magic" when the same scalar value appears in a single expression after the table is constructed. Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQL Data and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL
Post #1144580
 Posted Tuesday, July 19, 2011 5:17 PM
 SSCommitted Group: General Forum Members Last Login: Today @ 2:46 AM Points: 1,551, Visits: 4,823
 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.
Post #1144582
 Posted Tuesday, July 19, 2011 5:17 PM
 SSCoach Group: General Forum Members Last Login: Today @ 3:04 AM Points: 19,482, Visits: 13,068
 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 CirqueDeSQLeilI have given a name to my pain...MCM SQL Server 2008SQL RNNRPosting Performance Based Questions - Gail ShawPosting Data Etiquette - Jeff ModenHidden RBAR - Jeff ModenVLFs and the Tran Log - Kimberly Tripp
Post #1144583

 Permissions