Meaningful Names for Null Indicators
October 29, 2008 Ted Holt
There are two ways that a column of a SQL result set can contain the null value. One, it can be derived from an expression that contains a null value. Two, null is returned in outer joins and exception joins when a row in a primary table has no match in a secondary table. In either event, RPG has no way to represent a null value, so what’s a guy (a professional i-on-Power guy, that is) to do when an embedded SQL command might return null values? Since RPG variables cannot be null, SQL loads two-byte (five-digit) integer variables with zeros and negative ones. Zeros indicate non-null values. Negative ones indicate null values. These integers are properly called null indicators. To indicate that a null indicator corresponds to a data variable, follow the data variable with the null indicator in the FETCH command. Do not separate the two with a comma. exec sql fetch TheCursor into :Name :NameNullInd; Notice that both the data variable and the null indicator are prefixed with colons, as they both host variables. If the data variable is a data structure, use an array for the indicator variable. You can test individual array elements in order to determine if a column returned null or not. In the following example, the programmer tests element four of array NullsInds in order to determine whether the item description came back null or not.
D InvoiceData ds
D InvNbr 5p 0
D InvLineNbr 3p 0
D ItemNbr 6a
D ItemDescr 30a
D ItemPrice 5p 2
D QtySold 3p 0
D NullInds s 5i 0 dim(6)
D NullValue s like(NullInds) inz(-1)
/free
exec sql
declare Invoice cursor for
SELECT Ln.InvNbr, Ln.LineNbr,
Ln.ItemNbr, Itm.Descrip,
Ln.Price, Ln.Qty
FROM InvLine as Ln
LEFT JOIN Items as Itm
ON Ln.ItemNbr = Itm.Item;
*inlr = *on;
exec sql
open Invoice;
dow '1';
exec sql
fetch Invoice into :InvoiceData :NullInds;
if sqlstt = '02000';
leave;
endif;
if NullInds(4) = NullValue;
// do whatever
endif;
enddo;
exec sql
close invoice;
return;
For a short example, cryptic code is no big deal. In a larger program, you might find it advantageous to give the null indicator a name. You can do so by overlaying the array with a data structure. Here’s one way to accomplish that. D InvoiceData ds D InvNbr 5p 0 D InvLineNbr 3p 0 D ItemNbr 6a D ItemDescr 30a D ItemPrice 5p 2 D QtySold 3p 0 D NullInds s 5i 0 dim(6) D NullAddr s * inz(%addr(NullInds)) D InvoiceNulls ds based(NullAddr) D NullInvNbr like(NullInds) D NullLineNbr like(NullInds) D NullItemNbr like(NullInds) D NullItemDescr like(NullInds) D NullItemPrice like(NullInds) D NullQtySold like(NullInds) D NullValue s like(NullInds) inz(-1) /free exec sql declare Invoice cursor for SELECT Ln.InvNbr, Ln.LineNbr, Ln.ItemNbr, Itm.Descrip, Ln.Price, Ln.Qty FROM InvLine as Ln LEFT JOIN Items as Itm ON Ln.ItemNbr = Itm.Item; *inlr = *on; exec sql open Invoice; dow '1'; exec sql fetch Invoice into :InvoiceData :NullInds; if sqlstt = '02000'; leave; endif; if NullItemDescr = NullValue; // do whatever endif; enddo; exec sql close invoice; return; In this example, NullItemDesc is another name for the fourth element of NullInds.
|