Managing Null Indicators

This code snippet illustrates how to set up a null indicator array to use when retrieving a data set that might return null values. The indicators (in the data structure) are based on a pointer. The FETCH pops the data into the invoice data structure and the resulting indicators for the fields into the null indicator array.

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)
     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';                         

        if NullItemDescr = NullValue;                
           // do whatever                            

     exec sql           
        close invoice;  

The data structure can then be used to reference any of the separate data elements indicators that were returned as null.