The BOOLEAN data type

SQL implementation of the BOOLEAN data type varies on the database type.

Basics about BOOLEAN SQL type

BDL BOOLEAN values can be NULL, TRUE(1) or FALSE(0).

SQL Database vendor specific implementation of the boolean SQL type may not correspond exactly to the BDL BOOLEAN values.

For example, IBM® Informix® boolean SQL data type uses the 't' and 'f' values respectively to represent the true and false values.

However, BDL BOOLEAN variables can be used in SQL statements: The conversion from the BDL BOOLEAN values TRUE/1 and FALSE/0 to/from the SQL boolean values will be done by the database client or by the ODI driver.

Table 1. Boolean types support by database engines brands
Database Server Type Native boolean type
IBM DB2® LUW Yes, see details.
IBM Informix Yes, see details.
IBM Netezza Yes, see details.
Microsoft™ SQL Server Yes, as BIT data type.
Oracle® MySQL / MariaDB Yes, see details.
Oracle Database Server No, must use CHAR(1).
PostgreSQL Yes, see details.
SAP HANA® Yes, see details.
SQLite Yes, see details.

The TRUE and FALSE constants in SQL

The TRUE/FALSE constants are Genero language constants:
DEFINE b1, b2 BOOLEAN
LET b1 = TRUE
LET b2 = FALSE
The SQL syntax of the target database engine may not support the TRUE/FALSE keywords, for example in an statement such as:
INSERT INTO mytable (key,bcol) VALUES (455,TRUE)
In such case, consider using a BOOLEAN program variable for maximum SQL portability:
DEFINE rec RECORD
       key INTEGER,
       bcol BOOLEAN
   END RECORD
LET rec.key = 455
LET rec.bcol = TRUE
INSERT INTO mytable (key,bcol) VALUES (rec.*)