Home Equality check for two boolean expressions (TSQL)
Reply: 2

Equality check for two boolean expressions (TSQL)

Ivan Anatolievich
1#
Ivan Anatolievich Published in 2017-12-06 11:47:27Z

I have two tables and I want to join two tables based on the sign of the related columns

I'm joining on the condition a<0 = b<0 but the equality sign gives me a syntax error. I don't want to do (a<0 AND b<0) OR (Not a<0 AND Not b<0) because it doesn't look clean

Gordon Linoff
2#
Gordon Linoff Reply to 2017-12-06 11:48:56Z

You know what the answer is:

where (a < 0 and b < 0) or (a >= 0 and b >= 0)

SQL Server doesn't treat boolean values as bona fide values, so you cannot treat them as regular values in other types of expressions.

You can express this using bitwise or ('^') if you really want:

where (case when a < 0 and b < 0 then 1 else 0 end) ^ (case when a >= 0 and b >= 0 then 1 else 0 end) = 1

However, I find that rather inscrutable.

HABO
3#
HABO Reply to 2017-12-06 14:17:03Z

As Arvo suggested, you can use Sign(). Assuming that a and b are integer types:

where Sign( Sign( a ) + 1 ) = Sign( Sign( b ) + 1 )

Explanation: The inner Sign() calls convert the input values to -1, 0 or 1. Adding 1 shifts those values to 0, 1 or 2. The outer Sign() calls collapse that back to 0 or 1 representing negative and non-negative inputs.

This kind of code is occassionally useful, but should be accompanied by a comment explaining the intent. If the technique is truly impenetrable then it should be explained in the comment or a citation provided.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.31579 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO