Bookmark and Share

Recent Posts

Dealing With Nulls

May 22, 2014

Our most recent IBM i EXTRA article covers a bit about dealing with nulls when embedding SQL into RPG. But it is by no means a complete study of all the options.

That fact was quickly pointed out to us by reader Rusty Gadberry from Arkansas Data Services, who offered his much simpler solution. So we thought we'd introduce that solution here and start a discussion on when this might be an appropriate way to deal with nulls

Here's the suggested alternative to our logic:

   Exec SQL   

    Select CustID, CustName, ifnull (CustSales,dec(.00,7,2))

     Into :CustID, :CustName, :CustSales

     From Customer

     Where CustID = :InputCust;

Using the IfNull function, this shop avoids the possibility of indeterminate data coming into the program. We've used this approach sometimes, as well as a similar alternative approach using the Coalesce function shown below. In both cases, if the CustSales column value is null, it will be replaced with a value of zero in the result set returned to the RPG program. 

    Exec SQL

       Select CustID, CustName, Coalesce (CustSales,dec(.00,7,2))

         Into :CustID, :CustName, :CustSales

         From Customer

         Where CustID = :InputCust;

Approaches such as these are certainly simpler (and we like simple!) for those cases where the only logic we would have performed once we discovered a null is to replace the associated column value with a "generic" default.

However, neither of these approaches caters to cases where the "nullness" of a value is significant compared to zero (or some other "default" value). If there weren't such cases, then why were the columns made null-capable in the first place? Perhaps by default/mistake? Admittedly, in the very simplified example in the article, such a distinction is a bit of stretch.

It seems to us that it would often be significant in the case of outer joins to know about the presence of nulls, regardless of whether a column were otherwise null-capable or not. And, of course, date columns rarely have an easy or obvious default value to substitute. So we stand by the need to have a way to interrogate the nullness of a column in many circumstances.

Perhaps this is a good time to encourage you to think about null capability in your databases. Traditionally, it has been rare to have null-capable columns, but since there is a definite difference between the concept of "no value" and a zero, blank or other special value, perhaps we should consider more carefully which columns should be null-capable and which should not (i.e., a blank or zero value in this particular column is not significantly different to null).

Many shops these days are "modernizing" their databases, so this makes it a good time to consider making such changes if/when they make sense. Of course, in doing so, we must also potentially add to the logic of all our RPG (and other language) programs that access that data. We would certainly have to add logic to use %Null when using RPG's "native" I/O. Perhaps for such databases we could even mask this additional complexity by using an OA handler? For those programs using SQL to access the data, the techniques shown here or illustrated in the EXTRA article will need to be utilized.

Of course, you may have your own approaches, which we would love to see you share via the comments!

Posted May 22, 2014| Permalink

Post a Comment

Note: Comments are moderated and will not appear until approved

comments powered by Disqus