Re: SQL to determine if two numeric columns have the same sign.

As it turns out, sign() may not work....

The sign(0 function returns -1,1 or 0 if the value is zero. So if
only one of my columns has a zero value, the statement indicate that
the signs were mis-matched. With both my original ideas, having one
zero value didn't mean a mis-matched sign.

Need to discuss with the BA to determine which behavior we want...



Thanks!
Charles


On Thu, Sep 3, 2009 at 1:29 PM, Charles Wilt<charles.wilt@xxxxxxxxx> wrote:

<lol>

Yes, I suppose that would work....(head slapping sound :)

Thanks Birgitta, I didn't consider the function might already be there!

Charles

On Thu, Sep 3, 2009 at 1:20 PM, Birgitta Hauser<Hauser@xxxxxxxxxxxxxxx> wrote:

What about:

Select case when sign(Col1) = sign(col2) Then 'Yes' else 'No End

From MyTable;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Charles Wilt
Gesendet: Thursday, 03. September 2009 19:09
An: Midrange Systems Technical Discussion
Betreff: SQL to determine if two numeric columns have the same sign.

All,

I need an SQL statement to determine if two fields have the same sign.

There's the obvious..

select
  case
     when Fld1 <= 0 and Fld2 <= 0 then 'Yes'
     when Fld1 >= 0 and Fld2 >= 0 then 'Yes'
     else 'No'
  end as Same_Sign
from myFile

And the slightly tricky  (Though I haven't quite convinced  myself
that it'd work :)
select
  case
     when abs(fld1) + abs(fld2) = abs(fld1 + fld2) then 'Yes'
     else 'No'
  end as Same_Sign
from myFile


Anybody have any thoughts on either one of the above methods or an
alternative method?

The statement in question will be dealing with a couple of 80M record files.

Thanks!
Charles Wilt
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.