Exclusive or

On Mon, 26 Sep 2005 16:17:46 -0700, tshad wrote:

>"tshad" <tscheiderich@.ftsolutions.com> wrote in message
>news:eia2W5uwFHA.2312@.TK2MSFTNGP14.phx.gbl...
(snip)
>I guess the best you can do is:
>SELECT x from y
>where not (hours = 0 and amount = 0) and not (hours <> 0 and amount <> 0)
Hi Tom,
From this code, I gather that the results have to be the same if some
(but not all) fives change to sixes or sevens. Right?
If the values can only be 0 or > 0:
SELECT Hours, Amount
FROM MyTable
WHERE SIGN(Hours) <> SIGN(Amount)
If the values can also be < 0:
SELECT Hours, Amount
FROM MyTable
WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:qj7jj1ptaddk2049i141s2pn0v7ml60ag9@.
4ax.com...
> On Mon, 26 Sep 2005 16:17:46 -0700, tshad wrote:
>
> (snip)
> Hi Tom,
> From this code, I gather that the results have to be the same if some
> (but not all) fives change to sixes or sevens. Right?
> If the values can only be 0 or > 0:
> SELECT Hours, Amount
> FROM MyTable
> WHERE SIGN(Hours) <> SIGN(Amount)
I don't think this would work. I guess using 5 the issue.
Let me change it to:
hours = 0 amount = 0 no
hours = non-zero amount = 0 yes
hours = 0 amount = non-zero yes
hours = non-zero amount = non-zero no
So a zero in one value or the other. And they both cannot be zero and they
both can't be non-zero.
If you sql had an XOR operator, would be:
...when (hours = 0) XOR (amount=0)
(I'm sure Celko will jump in saying I am thinking like a programmer, again -
which I am because I am)
Tom
> If the values can also be < 0:
> SELECT Hours, Amount
> FROM MyTable
> WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 27 Sep 2005 15:08:00 -0700, tshad wrote:

>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:qj7jj1ptaddk2049i141s2pn0v7ml60ag9@.
4ax.com...
(snip)
>I don't think this would work.
Hi Tom,
Did you test it?
SELECT Hours, Amount
FROM (SELECT 0, 0 UNION ALL
SELECT 0, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 4) AS x(Hours, Amount)
WHERE SIGN(Hours) <> SIGN(Amount)
Hours Amount
-- --
0 1
2 0
SELECT Hours, Amount
FROM (SELECT 0, 0 UNION ALL
SELECT 0, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 4 UNION ALL
SELECT-5, 0 UNION ALL
SELECT 6,-7) AS x(Hours, Amount)
WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
Hours Amount
-- --
0 1
2 0
-5 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:gpjjj1928djumbcp2nt0mhsimo2eimurr0@.
4ax.com...
> On Tue, 27 Sep 2005 15:08:00 -0700, tshad wrote:
>
> (snip)
> Hi Tom,
> Did you test it?
I hadn't actually tested. But I have never used Sign before and at first,
after I figured out how it work, it didn't seem to work (in my head) for
(amount and hours equal to non-zero).
But I changed the select to:
SELECT Hours, SIGN(Hours) AS Expr1, amount, SIGN(amount) AS Expr2
FROM testtable2
WHERE (SIGN(Hours) <> SIGN(amount))
and than I saw why it worked.
Hours Expr1 amount Expr2
-- -- -- --
0 0 3 1
5 1 0 0
Clean.
Thanks,
Tom
> SELECT Hours, Amount
> FROM (SELECT 0, 0 UNION ALL
> SELECT 0, 1 UNION ALL
> SELECT 2, 0 UNION ALL
> SELECT 3, 4) AS x(Hours, Amount)
> WHERE SIGN(Hours) <> SIGN(Amount)
> Hours Amount
> -- --
> 0 1
> 2 0
>
> SELECT Hours, Amount
> FROM (SELECT 0, 0 UNION ALL
> SELECT 0, 1 UNION ALL
> SELECT 2, 0 UNION ALL
> SELECT 3, 4 UNION ALL
> SELECT-5, 0 UNION ALL
> SELECT 6,-7) AS x(Hours, Amount)
> WHERE (SIGN(Hours) + SIGN(Amount)) % 2 <> 0
> Hours Amount
> -- --
> 0 1
> 2 0
> -5 0
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 27 Sep 2005 16:20:49 -0700, tshad wrote:
(re: SIGN)
>Clean.
Hi Tom,
Thank Joe Celko for that. The first time I saw the SIGN function used in
a query was in either a usenet post or a book by Joe. I saw it, and
thought "nice - must add that to my bag of tricks that are seldom used
but can be very useful in specific situation".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:v7ulj1l4rdggdmdg43ip4nqgmt9qi4rc9f@.
4ax.com...
> On Tue, 27 Sep 2005 16:20:49 -0700, tshad wrote:
> (re: SIGN)
> Hi Tom,
> Thank Joe Celko for that.
OK.
I take back All (well, maybe not all) the things I have been saying about
Celko. :)
Thanks,
Tom
>The first time I saw the SIGN function used in
> a query was in either a usenet post or a book by Joe. I saw it, and
> thought "nice - must add that to my bag of tricks that are seldom used
> but can be very useful in specific situation".
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)