More fun with the Boolean data type

IBM i 7.5 introduces a synonym for NULL, UNKNOWN. Having performed a few tests, I can confirm I can use it just as I would NULL.

For example I can use it when inserting data into a SQL Table. The first column in this Table is a Boolean data type, the second is character.

INSERT INTO QTEMP.TESTTABLE VALUES('true','1'),('false','2'),(DEFAULT,'3'), ('true','4'),('false','5'),('true',UNKNOWN)

I have used a multiple rows insert to insert six rows with one Insert statement. Notice how I have used UNKNOWN in the data for the last row to be inserted.

I check the contents of this file with the following SQL statement:

SELECT * FROM TESTTABLE

I can see the rows I inserted with the null values, and the last row the UNKNOWN is shown as null:

COLUMN1 COLUMN2 ------- ------- true 1 false 2 <NULL> 3 true 4 false 5 true <NULL>

I am using ACS's Run SQL Scripts, therefore, the Boolean values are returned as 'true' and 'false'. If I was to use the STRSQL command I would only see '1' and '0'. IMHO this is another of many reason to stop using STRSQL.

If I wanted to find the rows in TESTTABLE that contain NULL in either column I could use the following statement:

SELECT * FROM TESTTABLE WHERE COLUMN1 IS NULL OR COLUMN2 IS NULL

Which returns the following rows:

COLUMN1 COLUMN2 ------- ------- <NULL> 3 true <NULL>

I can use UNKNOWN, rather than NULL, to get the same results:

SELECT * FROM TESTTABLE WHERE COLUMN1 IS UNKNOWN OR COLUMN2 IS UNKNOWN COLUMN1 COLUMN2 ------- ------- <NULL> 3 true <NULL>

I can also use the UNKNOWN to check for rows that are not null:

SELECT * FROM TESTTABLE WHERE COLUMN1 IS NOT UNKNOWN COLUMN1 COLUMN2 ------- ------- true 1 false 2 true 4 false 5 true <NULL>

Another way I can use a Boolean value is to check if a SQL statement returns results. For example:

VALUES (SELECT COLUMN2 FROM TESTTABLE WHERE COLUMN2 = '1')

Here I am using the Values statement to display whether a not there is a row in TESTTABLE where the second column is equal to '1'. The result is:

00001 ------ 1

What happens when I use the following Values statement when there not a row with the value of '99' in the Table?

VALUES (SELECT COLUMN2 FROM TESTTABLE WHERE COLUMN2 = '99')

As the is no matching row null is returned.

00001 ------ <NULL>

Let me use this is a more realistic example. First I need a new Table:

CREATE TABLE QTEMP.TESTTABLE2 (LOGICAL_VALUE BOOLEAN)

My new table contains just one column, a Boolean data type.

Let me modify my first Values statement into a Select to insert the result of the SQL Select statement in the parentheses ( ( ) )

INSERT INTO TESTTABLE2 (SELECT COLUMN2 FROM TESTTABLE WHERE COLUMN2 = '1')

When executed I see the following message returned:

Statement ran successfully 1 rows were affected by the statement

From that I know that a row was inserted into TESTTABLE2.

Now for the other Values statement converted into a Select:

INSERT INTO TESTTABLE2 (SELECT COLUMN2 FROM TESTTABLE WHERE COLUMN2 = '99')

When the statement is executed the message returned is:

Statement ran successfully 0 rows were affected by the statement

I cannot insert the row as the result is null.

This is confirmed when I look at the contents of TESTTABLE2:

SELECT * FROM TESTTABLE2 ; LOGICAL_VALUE ------------- true

There is only one row in the Table which indicates the first SQL statement returned a result. As there is no second row it shows that the result returned from the second SQL statement did not return a result.

I want to thank Sue Romano of IBM's Db2 for i team for sharing this information.

 

This article was written for IBM i 7.5 only, and will not work for earlier releases.