coalesce negative values
-
antonio.collins (7/10/2008)
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.
This could be a very simple UDF... but I need to know... where does the list come from and could it be passed to a function as a VARCHAR datatype?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The canonical way to do this is to OR the values to gether and then test the Negative bit (or just test the final result for negative). Unfortunately, there is no aggregate OR function built-in to SQL, and as we all know, writing your own in CLR is not nearly as fast.
[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] -
rbarryyoung (7/11/2008)
The canonical way to do this is to OR the values to gether and then test the Negative bit (or just test the final result for negative). Unfortunately, there is no aggregate OR function built-in to SQL, and as we all know, writing your own in CLR is not nearly as fast.But it is easy in T-SQL... that's why I was asking what the real form of the passed parameters were... makes a difference in how I'll write the split that will drive it all.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Since the original example is using a string-split function to break it into rows, I'm pretty sure that's the desired input. But I think he already has what he needs on this one.
- 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
-
GSquared (7/14/2008)
Since the original example is using a string-split function to break it into rows, I'm pretty sure that's the desired input. But I think he already has what he needs on this one.no, the values are float columns from the current row. stringing them together was a thought but i wanted something better.
select
...
case when 0 > ANY (select B.val_01 union select B.val_02 ... union select B.val_15) then -1 else 0 end as negVal1,
case when 0 > ANY (select B.another_01 union select B.another_02 union select B.another_03) then -1 else 0 end as negVal2,
...
the any subselect suggested by k.simmons has hardly any impact on performance and is straightforward to understand and maintain.
-
This looks like fun, so I'm chipping in...
declare @t table (id int identity(1, 1), a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int)insert @t
select 1, 1, 1, -1, 1, -1, -1, -1, 1
union all select 1, 1, 1, 1, 1, 1, 1, 1, 1
union all select 1, 1, 1, 1, 1, -1, 1, 1, 1
union all select 1, 2, 3, 4, 5, 6, 7, 8, 9
union all select 1, -2, 3, -4, 5, -6, 7, -8, 9
; with b as (
select id, case when sum(v) = sum(abs(v)) then 0 else 1 end as HasNegatives
from @t unpivot (v for c in ([a1], [a2], [a3], [a4], [a5], [a6], [a7], [a8], [a9])) as x
group by id)
select a.*, b.HasNegatives from @t a inner join b on a.id = b.id
/*
id a1 a2 a3 a4 a5 a6 a7 a8 a9 HasNegatives
------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------------
1 1 1 1 -1 1 -1 -1 -1 1 1
2 1 1 1 1 1 1 1 1 1 0
3 1 1 1 1 1 -1 1 1 1 1
4 1 2 3 4 5 6 7 8 9 0
5 1 -2 3 -4 5 -6 7 -8 9 1
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
If it's columns in a table, then you can use Unpivot and either the sum() - sum(abs()) calculation, or the 0 > Any function. Either will work.
Edit: And, had I been paying attention, I would have realized someone had beaten me to the punch on Unpivot.
- 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
-
RyanRandall (7/14/2008)
This looks like fun, so I'm chipping in...declare @t table (id int identity(1, 1), a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int)insert @t
select 1, 1, 1, -1, 1, -1, -1, -1, 1
union all select 1, 1, 1, 1, 1, 1, 1, 1, 1
union all select 1, 1, 1, 1, 1, -1, 1, 1, 1
union all select 1, 2, 3, 4, 5, 6, 7, 8, 9
union all select 1, -2, 3, -4, 5, -6, 7, -8, 9
; with b as (
select id, case when sum(v) = sum(abs(v)) then 0 else 1 end as HasNegatives
from @t unpivot (v for c in ([a1], [a2], [a3], [a4], [a5], [a6], [a7], [a8], [a9])) as x
group by id)
select a.*, b.HasNegatives from @t a inner join b on a.id = b.id
/*
id a1 a2 a3 a4 a5 a6 a7 a8 a9 HasNegatives
------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------------
1 1 1 1 -1 1 -1 -1 -1 1 1
2 1 1 1 1 1 1 1 1 1 0
3 1 1 1 1 1 -1 1 1 1 1
4 1 2 3 4 5 6 7 8 9 0
5 1 -2 3 -4 5 -6 7 -8 9 1
*/
Slightly simpler (I must be really bored today...)
select id, sign(sum(abs(v)-v)) as HasNegatives
...
____________________________________________________
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
-
Sign doesn't get him precisely what he's looking for, but it could probably be used. And it does reduce even further the typing required. 🙂
- 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
-
Just curious... the SIGN method with the subtraction from the ABS seems to work just fine as a negative number detector...
DECLARE @v-2 INTSET @v-2 = -10
SELECT ABS(@V)-@V, SIGN(ABS(@V)-@V) AS IsNegative
SET @v-2 = 10
SELECT ABS(@V)-@V, SIGN(ABS(@V)-@V) AS IsNegative
SET @v-2 = 0
SELECT ABS(@V)-@V, SIGN(ABS(@V)-@V) AS IsNegative
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
He's looking for a binary. Otherwise Sign would work. Possible that he can change his requirement, of course.
- 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
-
Ummmm, so convert the result to a Bit datatype... all that is being returned are 1's and 0's... no -1's...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
True.
- 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
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply