|
Meaningful Names for Null Indicators
Published: October 29, 2008
by 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.
|
