BOOLEAN Data Type

-- this syntax is used for testing the Boolean data type in SAP HANA

CREATE COLUMN TABLE TAB1 ( COL1 INT PRIMARY KEY, COL2 BOOLEAN, COL3 INT );
INSERT INTO TAB1 VALUES (1, TRUE, 1);
INSERT INTO TAB1 VALUES (2, FALSE, 0);
INSERT INTO TAB1 VALUES (3, UNKNOWN, NULL);

SELECT * FROM TAB1;

-- the following 2 INSERT statements will return datatype errors
INSERT INTO TAB1 VALUES (5, 1, 1);
INSERT INTO TAB1 VALUES (6, 0, 0);

INSERT INTO TAB1 VALUES (4, NULL, NULL);
SELECT * FROM TAB1;

SELECT * FROM TAB1 WHERE COL2 = TRUE;
SELECT * FROM TAB1 WHERE COL2 = FALSE;
-- the following 2 SELECT statements will return datatype errors
SELECT * FROM TAB1 WHERE COL2 = 1;
SELECT * FROM TAB1 WHERE COL2 = 0;

-- these lines will return 0 rows
SELECT * FROM TAB1 WHERE COL2 = UNKNOWN;
SELECT * FROM TAB1 WHERE NOT(COL2 IN (TRUE, FALSE));
SELECT * FROM TAB1 WHERE COL2 <> FALSE AND COL2 <> TRUE;

-- this is one option to return UNKNOWNS
SELECT * FROM TAB1 WHERE IFNULL(TO_INT(COL2), -1) = -1;