Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Turn negative numbers into 0 Expand / Collapse
Author
Message
Posted Monday, July 18, 2011 4:59 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, May 22, 2015 12:41 PM
Points: 656, Visits: 4,118
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 MyComplexCalc
FROM...

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 0
ELSE
COALESCE
(
here I take the first non-null from 4 conditions, the conditions are fairy complicated
)
END AS MyComplexCalc
FROM...


Post #1143810
Posted Monday, July 18, 2011 5:22 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 18, 2015 2:40 PM
Points: 2,801, Visits: 6,137
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


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, May 8, 2015 7:31 PM
Points: 9,928, Visits: 11,252
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 White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #1143827
Posted Monday, July 18, 2011 8:44 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 18, 2015 2:40 PM
Points: 2,801, Visits: 6,137
And also...


-- To make result = 0 when @test < 0

declare @test int -7

select (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


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, May 8, 2015 7:31 PM
Points: 9,928, Visits: 11,252
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 White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #1143850
Posted Monday, July 18, 2011 9:34 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, May 8, 2015 7:31 PM
Points: 9,928, Visits: 11,252
The Dixie Flatline (7/18/2011)
And also...
select (ABS(@test)+@test)/2 as result


Neat!




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #1143851
Posted Tuesday, July 19, 2011 5:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 1,937, Visits: 6,457
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 all
select null
from 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 problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw
  • Post #1144582
    Posted Tuesday, July 19, 2011 5:17 PM


    SSCoach

    SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

    Group: General Forum Members
    Last Login: Yesterday @ 11:38 AM
    Points: 18,656, Visits: 16,923
    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, MVP


    SQL RNNR

    Posting Performance Based Questions - Gail Shaw
    Post #1144583
    Posted Tuesday, July 19, 2011 5:36 PM


    SSCrazy Eights

    SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

    Group: General Forum Members
    Last Login: Friday, May 8, 2015 7:31 PM
    Points: 9,928, Visits: 11,252
    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 White
    SQLPerformance.com
    SQLblog.com
    @SQL_Kiwi
    Post #1144592
    Posted Tuesday, July 19, 2011 5:43 PM


    SSCrazy Eights

    SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

    Group: General Forum Members
    Last Login: Friday, May 8, 2015 7:31 PM
    Points: 9,928, Visits: 11,252
    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 White
    SQLPerformance.com
    SQLblog.com
    @SQL_Kiwi
    Post #1144596
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse