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.*)