SQL and null

In my last post, Handling null in RPG, I gave examples of how to cope with null values in RPG. As more of us are working with SQL we are going to need to know how to cope with nulls within it too.

Before I start I need to thank Paul Tuohy as my examples of using the null byte maps are based upon a presentation he gave at the OCEAN Technical Conference, in 2015.

Let me start with the basics, below is a SQL table with five columns (fields). I could have used the GENERATE_SQL SQL procedure to create this table from the DDS file I used in my previous story, you can learn about this SQL procedure here.

01 CREATE TABLE MYLIB/TESTTABLE ( 02 F001 CHAR(5) NOT NULL DEFAULT '', 03 F002 DATE DEFAULT NULL, 04 F003 DECIMAL(9,3), 05 F004 TIMESTAMP, 06 F005 NUMERIC(1,0), 07 UNIQUE (F001) 08 ) 09 RCDFMT TESTTABLER ;

In DDS I have to say which fields are allowed to contain nulls. In SQL when defining Tables the opposite is true, I have to define which columns will not allow null. Column F001, line 2, has the "NOT NULL" which means it cannot contain null. All of the other columns can contain null. I have used the "DEFAULT NULL" on line 3, for the other columns this is assumed. For those of you wondering what the difference is between using DECIMAL, line 4, and NUMERIC, line 6, when defining columns is F003 is packed numeric and F005 is signed numeric.

As I mentioned in my previous post about null IBM i do not contain the null value within the field or column itself. There is a null byte map within the file, with a position corresponding to each field/column in the file/table. In RPG the null byte map element is represented by the %NULLIND built in function. In SQL it is represented by a value returned to the program in an integer variable. Below is an example RPG program retrieving the null byte flag for F003 in the file I defined above.

01 dcl-s F1 char(5) ; 02 dcl-s F3 packed(9:3) ; 03 dcl-s F4 timestamp ; 04 dcl-s Null_F3 int(5) ; 05 exec sql DECLARE C0 CURSOR FOR SELECT F001,F003,F004 FROM TESTTABLE ORDER BY F001 FOR UPDATE ; 06 exec sql OPEN C0 ; 07 dow (1 = 1) ; 08 exec sql FETCH NEXT FROM C0 INTO :F1, :F3 :Null_F3, :F4 ; 09 if (SQLCOD <> 0) ; 10 leave ; 11 endif ; 12 F3 = 100 ; 13 exec sql UPDATE TESTTABLE SET F003 = :F3 WHERE CURRENT OF C0 ; 14 enddo ; 15 exec sql CLOSE C0 ;

The first thing to notice is that there is no need for a control option, which was needed when I was handling the nulls in RPG.

Lines 1 – 3: These variables will contain the values from F001, F003, and F004 when I fetch a row (record) from the table.

Line 4: This is the variable that will contain the null byte for F003. This is defined as a SHORT INTEGER type which is the equivalent of INT(5) in RPG.

Line 5: My cursor is declared. I will be fetching just three columns, F001, F003, and F004, sorting the cursor by F001, and I will be updating the cursor. In a declare if I do not define the cursor "FOR READ ONLY", "FOR UPDATE" is assumed. I just add it so that others can see that I will be updating the cursor.

Line 6: I open the cursor.

Line 7: Start of my Do-loop to fetch all the rows, the loop ends on line 13.

Line 8: This my fetch. Notice how in my declare I had defined three variables, but in my fetch I have four. The value of F001 is fetched into F1, value of F003 into F3, and F004 into F4. Where does the value for Null_F3 come from? Notice how there is no comma between F3 and Null_F3, this denotes that the null byte for F003 is retrieved into Null_F3. If the null byte is on Null_F3 will be -1, if it off it will be zero.

Lines 9 – 11: I know I will get comments and email about this, but I am checking the SQL code, SQLCOD, and if it not zero then I have either fetched all the rows or hit an error. In both cases I want to exit the Do-loop.

Line 12: Do I really need to describe what this line does?

Line 13: When I update the cursor as F3 contains a value when it is moved to F003 the null byte is turned off.

When I have fetched all the rows and I exit the Do-loop the cursor is closed on line 15.

If I wanted to set F003 to null I would the following update statement.

exec sql UPDATE TESTTABLE SET F003 = NULL WHERE CURRENT OF C0 ;

If I wanted to see the null byte map for all the columns I could code my fetch as I have above. But if the table has many fields I would not want to enter each column followed by the variable to contain the null byte. I would prefer to do a "SELECT *". Below shows how I can achieve that:

01 dcl-ds TableDs extname('TESTTABLE') qualified ; 02 end-ds ; 03 dcl-s NullInds int(5) dim(5) ; 04 dcl-ds NullDs based(NullPointer) qualified ; 05 F001 int(5) ; 06 F002 int(5) ; 07 F003 int(5) ; 08 F004 int(5) ; 09 F005 int(5) ; 10 end-ds ; 11 exec sql DECLARE C0 CURSOR FOR SELECT * FROM TESTTABLE ORDER BY F001 FOR READ ONLY ; 12 exec sql OPEN C0 ; 13 dow (1 = 1) ; 14 exec sql FETCH NEXT FROM C0 INTO :TableDs :NullInds ; 15 if (SQLCOD <> 0) ; 16 leave ; 17 endif ; 18 NullPointer = %addr(NullInds) ; 19 enddo ; 20 exec sql CLOSE C0 ;

Lines 1 – 2: This data structure is based upon the "input" file, therefore, I can use it in the fetch to contain all the values of all the columns in the table.

Line 3: This array will contain the null byte map for the table. As there are five columns it has five elements, dim(5).

Lines 4 – 10: I do not want to refer to the null bytes as elements in an array, I want to give each one its own name. I have defined this data structure with the same number of subfields as elements in the array. Notice that on line 4 BASED(NullPointer), I will explain what this is later.

Line 11: Here is the cursor declaration. This time I am only using the file for input so the declare contains "FOR READ ONLY".

Line 12: The cursor is opened.

Line 13: The Do-loop starts here, and ends on line 20.

Line 14: The fetch retrieves the values of the columns into the TableDs data structure and the null byte map into the NullInds array.

Line 15 – 17: I think all I need to say is that end of table processing happens here.

Line 18: By using RPG's %ADDR built in function I can point the values in the NullDs array to the pointer NullPointer, which points to the NullDs data structure. Now I can use the names of the subfields from the NullDs data structure for the null bytes, for example NullDs.F002.

Personally I do not use the null bytes. I use IFNULL, for example in the example program I would have coded the declare as:

exec sql DECLARE C0 CURSOR FOR SELECT F001,IFNULL(F003,0) FROM TESTTABLE ORDER BY F001 FOR READ ONLY ;

The IFNULL has two parameters, which are enclosed in parentheses and separated by a comma. The parameters are:

1. Column name
2. Value to replace the null

In the above example as F003 is a numeric column I am saying that when it is null move zero to the variable defined in the fetch statement.

IFNULL is not ANSI standard. In ANSI standard SQL I would have to use the COALESCE instead:

exec sql DECLARE C0 CURSOR FOR SELECT F001,COALESCE(F003,0) FROM TESTTABLE ORDER BY F001 FOR READ ONLY ;

As my SQLRPGLE is not going to be migrated to another platform, I prefer to use the IFNULL it is more apparent to other RPG developers what is happening.

The most common way I encounter null is when I join files with a "LEFT OUTER JOIN". This type of join joins the matching rows in the first file/table to the matching record/row in the second file/table. But if there is no matching record/row null is returned for all of the columns from that file/table.

Below is a typical View using the "LEFT OUTER JOIN" to join two files:

01 CREATE VIEW MYLIB/TESTVIEW ( 02 FIELD_1 FOR "F001", 03 CODE, 04 DESCRIPTION FOR "DESCRIPTN" 05 ) 06 AS SELECT A.F001,A.F002,B.DESCRIPTN 07 FROM TESTFILE A LEFT OUTER JOIN TESTFILE1 B 08 ON A.F002 = B.CODE 09 RCDFMT TESTVIEWR ;

When I look at the View I see null in the Description column in the fourth row, as there is no record in TESTFILE1 to match the row from TESTFILE.

FIELD_1 CODE DESCRIPTION 1 1 FIRST 2 2 SECOND 3 3 THIRD 4 4 -

If I change the Select definition and add the IFNULL like this:

01 CREATE VIEW MYLIB/TESTVIEW ( 02 FIELD_1 FOR "F001", 03 CODE, 04 DESCRIPTION FOR "DESCRIPTN" 05 ) 06 AS SELECT A.F001,A.F002, 07 IFNULL(B.DESCRIPTN,'** No match **') 08 FROM TESTFILE A LEFT OUTER JOIN TESTFILE1 B 09 ON A.F002 = B.CODE 10 RCDFMT TESTVIEWR ;

When I look at my view I now have what I consider to be a more meaningful value in the Description column of the fourth row:

FIELD_1 CODE DESCRIPTION 1 1 FIRST 2 2 SECOND 3 3 THIRD 4 4 ** No match **

Now you know how to cope with null in RPG and in SQL. You no longer need to fear nothingness in the form of nulls.

 

You can learn more about this from the IBM website:

• CREATE TABLE
• IFNULL
• COALESCE
• Reading a SQL table in SQL
• Build Views and Views of Views

 

This article was written for IBM i 7.2, and should work for earlier releases too.