Home Equality check for two boolean expressions (TSQL)

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#
 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.