SQL boolean comparison
-
Interesting, why this...
SELECT
COUNT(*)
FROM TBL1
LEFT JOIN TBL2 ON TBL2.ID = TBL1.TBL2_ID
WHERE (TBL1.INTFIELD IS NULL) <> (TBL2.INTFIELD IS NULL)... Ends in an error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '<'.Where I'm wrong? Is it not allowed to compare two booleans? At least in (MS)SQL not :doh:
Try using a case statement in the where clause to turn the bool in 1 or 0 where (case when intfield is null then 0 else 1 end) != (case when intfield is null then 0 else 1 end) (why do you have int field that are null?)
Never underestimate the power of human stupidity RAH
-
Try using a case statement in the where clause to turn the bool in 1 or 0 where (case when intfield is null then 0 else 1 end) != (case when intfield is null then 0 else 1 end) (why do you have int field that are null?)
Never underestimate the power of human stupidity RAH
-
Yes seems so in SQL. But e.g. in c++ (and I think also in c#) you can easy have something like:
bool b1= true;
bool b2= false;
bool res= b1 == b2; -
Interesting, why this...
SELECT
COUNT(*)
FROM TBL1
LEFT JOIN TBL2 ON TBL2.ID = TBL1.TBL2_ID
WHERE (TBL1.INTFIELD IS NULL) <> (TBL2.INTFIELD IS NULL)... Ends in an error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '<'.Where I'm wrong? Is it not allowed to compare two booleans? At least in (MS)SQL not :doh:
You need a logical operator to combine two boolean conditions. In this example, you'd want the
Xoroperator. But unfortunately, MS SQL Server doesn't support it; it only provides a bitwiseXoroperator, which won't work here. You can either useCASE, as Mycroft suggested, or the longhand version ofXor:a XOR b <==> (a AND (NOT b)) OR ((NOT a) AND b)
... WHERE ((TBL1.INTFIELD Is Null And TBL2.INTFIELD Is Not Null) Or (TBL1.INTFIELD Is Not Null And TBL2.INTFIELD Is Null))
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You need a logical operator to combine two boolean conditions. In this example, you'd want the
Xoroperator. But unfortunately, MS SQL Server doesn't support it; it only provides a bitwiseXoroperator, which won't work here. You can either useCASE, as Mycroft suggested, or the longhand version ofXor:a XOR b <==> (a AND (NOT b)) OR ((NOT a) AND b)
... WHERE ((TBL1.INTFIELD Is Null And TBL2.INTFIELD Is Not Null) Or (TBL1.INTFIELD Is Not Null And TBL2.INTFIELD Is Null))
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You need a logical operator to combine two boolean conditions. In this example, you'd want the
Xoroperator. But unfortunately, MS SQL Server doesn't support it; it only provides a bitwiseXoroperator, which won't work here. You can either useCASE, as Mycroft suggested, or the longhand version ofXor:a XOR b <==> (a AND (NOT b)) OR ((NOT a) AND b)
... WHERE ((TBL1.INTFIELD Is Null And TBL2.INTFIELD Is Not Null) Or (TBL1.INTFIELD Is Not Null And TBL2.INTFIELD Is Null))
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
NULLs cannot be compared in SQL
