Null Indicators

Trapping null values.

here 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?

FETCH may return null

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.