Wednesday, April 6, 2016

SQL and null

sql 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:

 

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

11 comments:

  1. Another great step into the future of the Power i

    ReplyDelete
  2. Very good and thanks for sharing. One note I would like to contribute about checking SQLCOD. I have seen many examples on many websites where SQLCOD is compare to zero to see if there is an error or end of file condition. There is an issue with that approach that can result in premature exit of the cursor loop. Positive values other than 100 are informational messages and typically should not drive exit of the loop. Checking for zero does not account for those positive / informational messages. We learned this the hard way a few years back in my shop as we had random issues where data was not updated for some rows. After we were able to reproduce the problem in test we found this was the cause.

    ReplyDelete
  3. Ifnull works great but sometimes I have a sub query which results in nulls. When I deal with this I use a case statement

    select blah
    ,case when (select sublah from mytable where blah = blech) is null then 0
    else (select sublah from mytable where blah = blech)
    end
    from mybigtable

    thanks kevin dunham from linkdedin

    ReplyDelete
  4. here is how I handle nulls

    dcl-s gSqlIndArray int(5) dim(200) inz ;

    exec SQL
    fetch Next
    from InvHstCursor
    into :invhstpfDs:gSqlIndArray ;

    ReplyDelete
  5. If you clear the structure before the fetch, you don't have to be concerned about null fields. If the field is null, it will not be populated in the structure by the fetch. Since the structure was cleared before the fetch fields can referenced without testing for a null condition.

    dcl-ds sch extname('SCHED_JOB') qualified;
    end-ds;
    dcl-s nullInd int(5) dim(28);

    dou EOF;
    clear sch;
    EXEC SQL
    FETCH IMPIBMSCH_C1
    INTO :sch :nullind;
    if SQLSTATE <> SQLSTTOKAY;
    leave;
    endif;

    somedate = sch.schdate; // null capable field
    enddo;

    ReplyDelete
  6. What about using null in the select portion of the statement? For example:

    select name, ethnicity, age, favourite_number
    from person
    union all
    select name, breed, age, null
    from pet;

    IBM i (7.4) complains about this and I really don't want to use 0 (zero) or 999 or some other numeric value. I would like it to be null.

    The error is

    Message: [SQL0206] Column or global variable NULL not found.

    I can get around this by (1) using a variable and set its value to null or (2) include a table in my query with a column containing a null value, but I'm wondering if there is another, more elegant, way.

    ReplyDelete
    Replies
    1. I would try either the DEFAULT keyword or '' (two ' with nothing between them).

      Delete
    2. Thanks, Simon.

      Using DEFAULT results in "Message: [SQL0206] Column or global variable DEFAULT not found."

      Using '' (two ' with nothing between them) - a zero-length string? - results in a dataset with red pluses:

      Pierre, French, 51, 8
      Max, Bulldog, 3, ++++++++++++

      and "Message: [SQL0420] Value for cast argument not valid."

      Delete
    3. Would these results be due to configuration or am I missing something?

      Delete
    4. You would use the default like:
      I have a post showing how to use the DEFAULT in a Insert here.

      Delete

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.