Tech Tip: Casting Boolean values in SQL statements

PRODUCT: 4D | VERSION: 11 | PLATFORM: Mac & Win

Published On: March 25, 2009

The SQL engine in 4D v11 SQL does not support implicit type casting between numeric (or string) and Boolean types. Instead, you will have to use the SQL CAST function. Two examples of how to do that follow:

SELECT * FROM Table_1 WHERE Field_3 = CAST ( 0 AS BOOLEAN )

In the above example, the integer 0 will be cast as a FALSE boolean value. In the following example, the string 'false' will be cast as a FALSE value:

SELECT * FROM Table_1 WHERE Field_3 = CAST ( 'false' AS BOOLEAN )

Commented by Silvio Belini on March 30, 2009 at 9:12 AM

This is important info, since it's a change of behaviour from 4D 2004.
Back in 4D 2004, implicit type casting in SQL statements was supported (i.e. the statement "SELECT * FROM Table_1 WHERE Field_3 = 0" would work fine). However, in 4D v11 SQL the code has to be changed accordingly (using the CAST function).
The same has to be done on external code accessing 4D v11 SQL (i.e. PHP).