• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Quadrant Software:  Going paperless pays off! Watch our exclusive Webinar series
    COMMON:  Join us at the 2009 annual meeting and expo, April 26-30, Reno, Nevada
    Vision Solutions:  A $20 gas card for completing a short i5/OS DR survey

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    Global’s Executive Dash Gets Closer Hooks to Excel Is the Smart Cube the New i?

    Leave a Reply Cancel reply

Volume 8, Number 37 -- October 29, 2008
THIS ISSUE SPONSORED BY:

ProData Computer Services
Aldon
WorksRight Software

Table of Contents

  • Calculate a Fractional Number of Years Difference Between Two Dates in SQL
  • Meaningful Names for Null Indicators
  • Pimp My Font Size, Win a No-Prize

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle