How to cast an integer to a boolean in a MySQL SELECT clause?
Try using the IF function:
SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, TRUE, FALSE)
FROM ...
or
SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, 1, 0)
FROM ...
Even without the IF function, running
mysql> select ('rolando' = 'rolando') str_compare;
+-------------+
| str_compare |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql>
yield 0 or 1 using the mysql client
The problem is this: CAST() and CONVERT() can only accept and return the following types:
- BINARY[(N)]
- CHAR[(N)]
- DATE
- DATETIME
- DECIMAL[(M[,D])]
- SIGNED [INTEGER]
- TIME
- UNSIGNED [INTEGER]
Since BOOLEAN is not in this list, it can never be returned by CAST or CONVERT
You could use the IF function to generate strings
SELECT IF(`gu`.`StoppingUnitEventME`=`ese`.`MonitoringElement`, 'TRUE', 'FALSE')
FROM ...
You can do it very simple way, without use of redundant IF() statement:
... `YourField` IS NOT NULL AS `YourField` ...
You can also try the classic boolean coercion:
SELECT NOT NOT (whatever);
The good thing about this is that it naturally preserves NULLs, whereas most of the answers here do not.
If you want to coerce NULL to FALSE, then do
SELECT IFNULL(NOT NOT (whatever), FALSE);