coalesce negative values

Question

Post reply

coalesce negative values

antonio.collins

SSCrazy Eights

Points: 9600
More actions
July 10, 2008 at 7:50 pm

#189389

before i write some awful case statements or multiple UDFs, is there any direct way to determine if any values in a list are negative?
coalesce_negative( 1, 1, 1,-1, 1,-1,-1,-1, 1) would return -1.
i can't just multiply the values together since -1 * -1 * 1 * 1 = 1. sometimes i need to coalesce 4 values, sometimes 5, sometimes 15 and since UDFs don't allow optional parameters, i'd wind up needing 3 UDFs (and a new UDF if 12 values need to be checked.
the logic has to be used as part of a select so a stored proc is not suitable.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

It depends on your list. Is it a string or does each value come from a field?

Here is a way to get it if the list is a concatenated string.

Declare @List varchar(20)

Set @list ='1,1,1,-3,-2,-1,4,5'

Select substring(@list,

charindex('-',@list),

charindex(',',substring(@list,charindex('-',@list),len(@list))) -1

)

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 2

I know you said you did not wan to write an awful case statement, but if the values are coming from database fields you could do something like this.

Declare @Val1 int

Declare @Val2 int

Declare @Val3 int

Declare @Val4 int

Declare @Val5 int

Set @Val1 = 1

Set @Val2 = 1

Set @Val3 = -1

Set @Val4 = 1

Set @Val5 = 1

Select coalesce(Case WHEN @Val1 > -1 THEN NULL ELSE @Val1 END,

Case WHEN @Val2 > -1 THEN NULL ELSE @Val2 END,

Case WHEN @Val3 > -1 THEN NULL ELSE @Val3 END,

Case WHEN @Val4 > -1 THEN NULL ELSE @Val4 END,

Case WHEN @Val5 > -1 THEN NULL ELSE @Val5 END)

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 3

the list is individual columns of floats. i thought of stringing them together and using my fListToFloats(list,delim) function but i'd like something cleaner, especially since i need to do the calculation with multiple sets in a query.

select ...

min(sign(M1.item)) as negVal1,

min(sign(M2.item)) as negVal2,

...

from ...

join {table} B

cross apply global.dbo.fListToFloats(

cast(B.val_01 as varchar(12))+','

+ cast(B.val_02 as varchar(12))+','

...

+ cast(B.val_15 as varchar(12)), ',' ) as M1

cross apply global.dbo.fListToFloats(

cast(B.another_01 as varchar(12))+','

+ cast(B.another_02 as varchar(12))+','

...

+ cast(B.another_05 as varchar(12)), ',' ) as M2

...

if ANY could be applied to a list (rather than a SELECT), it would be perfect.

select

...

case when 0 > ANY (B.val_01, B.val_02, ... B.val_15) then -1 else 0 end as negVal1,

case when 0 > ANY (B.another_01, B.another_02, ... B.another_03) then -1 else 0 end as negVal2,

...

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 4

You can use any, but you would have to do it like this.

Declare @Val1 int

Declare @Val2 int

Declare @Val3 int

Declare @Val4 int

Declare @Val5 int

Set @Val1 = 1

Set @Val2 = 1

Set @Val3 = -1

Set @Val4 = 1

Set @Val5 = 1

Select CASE WHEN 0 > any ( Select @val1 UNION ALL Select @val2 UNION ALL Select @val3) THEN -1 Else 0 END

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 5

DECLARE @T TABLE (ID INT)

INSERT INTO @T

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT -1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT -1

SELECT MIN(ID) MinVal FROM @T

SELECT COALESCE(MIN(ID),'') MinVal FROM @T

SELECT MIN(ID) MinVal FROM

(

SELECT -10 ID UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 0

) AS Z

Cheers,
Hari
Tips & Tricks for SQL BI Developers

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

antonio.collins (7/10/2008)
the list is individual columns of floats. i thought of stringing them together and using my fListToFloats(list,delim) function but i'd like something cleaner, especially since i need to do the calculation with multiple sets in a query.
select ...
min(sign(M1.item)) as negVal1,
min(sign(M2.item)) as negVal2,
...
from ...
join {table} B
cross apply global.dbo.fListToFloats(
cast(B.val_01 as varchar(12))+','
+ cast(B.val_02 as varchar(12))+','
...
+ cast(B.val_15 as varchar(12)), ',' ) as M1
cross apply global.dbo.fListToFloats(
cast(B.another_01 as varchar(12))+','
+ cast(B.another_02 as varchar(12))+','
...
+ cast(B.another_05 as varchar(12)), ',' ) as M2
...
if ANY could be applied to a list (rather than a SELECT), it would be perfect.
select
...
case when 0 > ANY (B.val_01, B.val_02, ... B.val_15) then -1 else 0 end as negVal1,
case when 0 > ANY (B.another_01, B.another_02, ... B.another_03) then -1 else 0 end as negVal2,
...

Maybe this?

select

...

SIGN( (B.val_01-ABS(B.val_01)) + (B.val_02-ABS(B.val_02)) + ... + (B.val_15-ABS(B.val_15))) as negVal1,

...

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 7

Ken Simmons (7/10/2008)
You can use any, but you would have to do it like this.
Declare @Val1 int
Declare @Val2 int
Declare @Val3 int
Declare @Val4 int
Declare @Val5 int
Set @Val1 = 1
Set @Val2 = 1
Set @Val3 = -1
Set @Val4 = 1
Set @Val5 = 1
Select CASE WHEN 0 > any ( Select @val1 UNION ALL Select @val2 UNION ALL Select @val3) THEN -1 Else 0 END

i'll check how ANY and the subSELECT UNIONS affects the execution plan and performance.

select ...

case when 0 > any (select val01 union select val02 union ... select val15) then -1 else 0 end as negVal01,

...

from ...

{table} B

...

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 8

antonio.collins (7/11/2008)
Ken Simmons (7/10/2008)
You can use any, but you would have to do it like this.
Select CASE WHEN 0 > any ( Select @val1 UNION ALL Select @val2 UNION ALL Select @val3) THEN -1 Else 0 END[/code]
i'll check how ANY and the subSELECT UNIONS affects the execution plan and performance.
select ...
case when 0 > any (select val01 union select val02 union ... select val15) then -1 else 0 end as negVal01,
...
from ...
{table} B
...

Hey Ken,

I finished implementing and testing the queries with the ANY subSELECT. The plan looks awful, but it hardly affects peformance at all since all subSELECTed values are on the current row. I'll retest with a

CASE WHEN val01 < 0 THEN -1 WHEN val02 < 0 THEN -1 ... WHEN val15 < 0 THEN -1 END

but I doubt if it will make a difference performance-wise either.

Thanks for the suggestions.

GSquared SSC Guru Points: 260824 More actions · Answer 9

Compare the sum of the list, and sum of the absolute values of the list. If it isn't equal, one or more value are negative.

select case

when

(select sum(values)

from table) =

(select sum(abs(values))

from table) then

else

end

Or however you want to resolve it. Won't matter how many values it is.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 10

GSquared (7/11/2008)
Compare the sum of the list, and sum of the absolute values of the list. If it isn't equal, one or more value are negative.
select case
when
(select sum(values)
from table) =
(select sum(abs(values))
from table) then
else
end
Or however you want to resolve it. Won't matter how many values it is.

that's about as verbose as the case statement and more verbose than the ANY subSELECT. my fingers already hurt! 😉

GSquared SSC Guru Points: 260824 More actions · Answer 11

Yeah, two sub-queries and an equality test is pretty tough. I know how it goes. 🙂

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

I think that this works too:

Select Count(NullIf(values,-1))-Count(values) From table

Probably not any faster than Gus's, but I guess it'll help those out aching fingers 🙂

Of course, now that I look at it, I think that Gus's could be more "finger-friendly" too.

select case

when

(select sum(values)-sum(abs(values)) from table)=0

then

else

end

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 13

The NullIf thing will only work if the value is -1 (or am I missing something). I'm assuming the possibility exists for any valid negative integer.

And yes, subtracting the two does eliminate a few words and a table scan from the query. Good idea. (Heck with the finger-saving, the IO difference is where it really matters.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

GSquared (7/11/2008)
The NullIf thing will only work if the value is -1 (or am I missing something). I'm assuming the possibility exists for any valid negative integer.

Oops, you are correct. Thanks...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]