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: 2 days ago @ 12:49 PM Points: 660, Visits: 4,532
 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: Tuesday, November 29, 2016 2:42 PM Points: 3,370, Visits: 6,889
 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
Post #1143813
 Posted Monday, July 18, 2011 6:33 PM
 SSCrazy Eights Group: General Forum Members Last Login: Friday, November 11, 2016 6:39 AM Points: 9,932, Visits: 11,346
 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
Post #1143827
 Posted Monday, July 18, 2011 8:44 PM
 Hall of Fame Group: General Forum Members Last Login: Tuesday, November 29, 2016 2:42 PM Points: 3,370, Visits: 6,889
 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
Post #1143838
 Posted Monday, July 18, 2011 9:32 PM
 SSCrazy Eights Group: General Forum Members Last Login: Friday, November 11, 2016 6:39 AM Points: 9,932, Visits: 11,346
 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
Post #1143850
 Posted Monday, July 18, 2011 9:34 PM
 SSCrazy Eights Group: General Forum Members Last Login: Friday, November 11, 2016 6:39 AM Points: 9,932, Visits: 11,346
 The Dixie Flatline (7/18/2011)And also...`select (ABS(@test)+@test)/2 as result`Neat! Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi
Post #1143851
 Posted Tuesday, July 19, 2011 5:17 PM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 5:21 PM Points: 2,193, Visits: 7,775
 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
Post #1144582
 Posted Tuesday, July 19, 2011 5:17 PM
 SSC-Insane Group: General Forum Members Last Login: Monday, November 21, 2016 11:03 AM Points: 20,009, Visits: 18,255
 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, MVPSQL RNNRPosting Performance Based Questions - Gail Shaw
Post #1144583
 Posted Tuesday, July 19, 2011 5:36 PM
 SSCrazy Eights Group: General Forum Members Last Login: Friday, November 11, 2016 6:39 AM Points: 9,932, Visits: 11,346
 mister.magoo (7/19/2011)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.I've seen Stefan use that before too. IIRC though, you ought not use WHERE 1 = 0 in the recursive part because contradiction detection in QO kicks in and removes the clause completely. If you try that example code with the four variables, you'll see a plan with a Constant Scan and a Compute Scalar - not the usual recursive structure with its stacking index spool and so on. Nevertheless, even with the contradiction in place, the Constant Scan does evaluate the core expression and the Compute Scalar references that twice. This is no guarantee that the core expression won't be evaluated twice - the following 'bug' for example is not fixed in any current build (though it is fixed in Denali CTP3):https://connect.microsoft.com/SQLServer/feedback/details/636382/scalar-expression-evaluated-twice-with-sum-aggregate Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi
Post #1144592
 Posted Tuesday, July 19, 2011 5:43 PM
 SSCrazy Eights Group: General Forum Members Last Login: Friday, November 11, 2016 6:39 AM Points: 9,932, Visits: 11,346
 CELKO (7/19/2011)I am hoping that the optimizer will "do magic" when the same scalar value appears in a single expression after the table is constructed.Generally speaking, the optimizer is not nearly as smart in this area as we should perhaps expect. In SQL Server, scalar computations are not really costed or optimized at all. The thinking was that simple scalars were always ridiculously cheap - an assumption that rarely holds water these days (functions, for example, may be arbitrarily complex). There do appear to have been a few minor improvements for Denali (see my reply to Mr Magoo) but nothing like a fully generalized expression service or manager which would optimize for the number of expression evaluations. No doubt there are concerns here around backward compatibility (especially for non-deterministic functions like NEWID) but even so... Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi
Post #1144596

 Permissions