Set values less than zero = 0? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Set values less than zero = 0?

Author

Topic

malachi151
Posting Yak Master

152 Posts

Posted - 2010-04-26 : 11:43:37

I need to update a table with some calculated values, but the only valid values are positive. If the value is negative, then we set it to zero. The best I can tell the only way to do this is to use a CASE statement and test each of the values to see if it’s less than zero, and if so then set it to zero, otherwise use the value.

This seems both high inefficient (working on hundreds of columns in millions of rows) and makes for cumbersome code. I can make a UDF to resolve problem #2 to streamline the code, but it’s still slow.

Is there any better way to do this? I looked at the FLOOR function, but it doesn't seem that this really does what I'm looking for. Is there some math function that I'm missing?

davebatt
Starting Member

17 Posts

Posted - 2010-04-26 : 12:42:08

somethign like update table set value = 0 where value < 0

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-26 : 13:23:47

(working on hundreds of columns in millions of rows)
Do you have tables with hundreds of columns?
Or do you mean hundreds rows with affected columns in millions of rows.

No, you're never too old to Yak'n'Roll if you're too young to die.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 13:30:21

There is no efficient way to do it as SQL Server will have to scan the data. Make sure you update the application or stored procedure to prevent this in the future. You should also add a check constraint to ensure the negative data doesn't get added to the column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

malachi151
Posting Yak Master

152 Posts

Posted - 2010-04-26 : 13:48:37

To clarify, a current update would look something like this:

UPDATE t
SET
column1 = (SELECT ISNULL(SUM(columnx)+SUM(columny)*factorz, 0) FROM TableB WHERE A, B, C GROUP BY Q, R, S, factorz)
FROM TableA t

This is just a vague example, the real update would have from 10 to 100+ columns being updated like this and it wouldn't be such a simple aggregation. The problem is that the result of the subquery in this can can be negative. If it is, then I need the result to be zero, not the negative number.

The only way I see to do this currently is:

UPDATE t
SET
column1 = (SELECT
CASE WHEN ISNULL(SUM(columnx)+SUM(columny)*factorz, 0) < 0 THEN 0 ELSE ISNULL(SUM(columnx)+SUM(columny)*factorz, 0) END
FROM TableB WHERE A, B, C GROUP BY Q, R, S, factorz)
FROM TableA t

That just seems like a really bad way to have to do this.

I was wondering if there was some function that would do something like:

UPDATE t
SET
column1 = (SELECT
FLOOR(ISNULL(SUM(columnx)+SUM(columny)*factorz, 0), 0)
FROM TableB WHERE A, B, C GROUP BY Q, R, S, factorz)
FROM TableA t

Except I know that the FLOOR function doesn't work that way, but I'm looking for something like that. My alternative is to write a UDF something like:

CREATE FUNCTION SetMin(@value1 decimal, @value2 decimal)
RETURNS decimal
AS
BEGIN
RETURN CASE WHEN @value1 < @value2 THEN @value2 ELSE @value1 END
END

But that just doesn't seem like an efficient way to do it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 13:54:00

When you use the term "efficient" do you mean well-written code or are you referring to the performance of the query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-26 : 14:13:28

quote:
Originally posted by malachi151

I need to update a table with some calculated values, but the only valid values are positive. If the value is negative, then we set it to zero. The best I can tell the only way to do this is to use a CASE statement and test each of the values to see if it’s less than zero, and if so then set it to zero, otherwise use the value.

This seems both high inefficient (working on hundreds of columns in millions of rows) and makes for cumbersome code.

Are you doing something like this:

UPDATE tableName
SET columnName = CASE WHEN ComplexCalculation < 0 THEN 0 
		ELSE ComplexCalculation END

If so, then you can do this instead, which means the complex calculations are only done once. The CASE statement won't cause much of a performance hit, but performing the calculations twice might.

UPDATE z
SET columnName = CASE WHEN CalcReselt < 0 THEN 0
		ELSE CalcReselt END
FROM (	SELECT ComplexCalculation AS CalcReselt
		FROM tableName ) z

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

malachi151
Posting Yak Master

152 Posts

Posted - 2010-04-26 : 18:07:54

I'm just opting for the UDF approach, since apparently there is no better way.

CREATE FUNCTION [dbo].[GreatestDecimal](@value1 decimal(19,4), @value2 decimal(19,4))
RETURNS decimal(19,4)
AS
BEGIN
RETURN (CASE WHEN @value1 > @value2 THEN ISNULL(@value1,0.0) ELSE ISNULL(@value2,0.0) END)
END

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-26 : 18:24:00

quote:
Originally posted by malachi151

I'm just opting for the UDF approach, since apparently there is no better way.

CREATE FUNCTION [dbo].[GreatestDecimal](@value1 decimal(19,4), @value2 decimal(19,4))
RETURNS decimal(19,4)
AS
BEGIN
RETURN (CASE WHEN @value1 > @value2 THEN ISNULL(@value1,0.0) ELSE ISNULL(@value2,0.0) END)
END

Did you try the alternative I just offered you? It should work, and won't have the overhead of a function call.

Also, in your function, "ISNULL(@value1,0.0)", the ISNULL is redundant. In @value1 is NULL, the comparison will be false, and the ELSE term will be returned.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

timnorvel
Starting Member

2 Posts

Posted - 2011-08-18 : 15:40:06

Hey DBA in the making - i know this is an old post but i wanted to thank you. That's exactly what I was looking for. I'm not sure why the original poster didn't see the beauty, but I do. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources