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: Yesterday @ 2:24 PM
Points: 655, Visits: 3,916
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: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1143827
Posted Monday, July 18, 2011 8:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:27 PM
Points: 1,945, Visits: 3,068
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 Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1143834
Posted Monday, July 18, 2011 8:44 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
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, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
The Dixie Flatline (7/18/2011)
And also...
select (ABS(@test)+@test)/2 as result


Neat!




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1143851
Posted Tuesday, July 19, 2011 5:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 1:27 PM
Points: 1,945, Visits: 3,068
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-SQL
I 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 Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1144580
Posted Tuesday, July 19, 2011 5:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:32 PM
Points: 1,796, Visits: 5,801
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


  • MMGrid Addin
  • MMNose Addin


  • 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 @ 8:18 PM
    Points: 17,824, Visits: 15,756
    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
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse