Pages

Friday, June 17, 2022

More fun with the Boolean data type

using unknown instead of null in sql

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.

4 comments:

  1. Hi Simon! Can I use this: SELECT COLUMN2 FROM TESTTABLE WHERE COLUMN2 ? to select all column2 = '1' Best Regards, Jose

    ReplyDelete
    Replies
    1. If COLUMN2 is Boolean yes you can use that statement.

      Delete
  2. Hi Simon! Can I use: UPDATE TESTFILE SET COL1 = COL2 > 500, when COL1 is boolean? Best Regards, Jose

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.