• 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
    Manta Technologies

    The Leader in IBM i Education!
    Need training on anything i?
    Manta is all you need.

    During the month of May, when you purchase a license for the Free-Form RPG Programming series for any term (one, two, or three years) and any user license level, and you will get the equivalent license for the Coding Free-Form RPG course for FREE.

    130 courses and competency exams on:
    · IBM i operations
    · System Management and Security
    · IBM i Programming Tools
    · Programming in RPG, COBOL, CL, Java
    · Web Development
    · SQL, DB2, Query

    Product features:
    · Runs in every popular browser
    · Available 24/7/365
    · Free Student Reference Guides
    · Free Student Administration
    · Concurrent User License
    · Built-In IBM i Simulator

    You can download our 200-page catalog and take sample sessions at MantaTech.com.

    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

  • POWERUp Brings IBM i Base Back Together in the Big Easy
  • New Nav for i Brings New Stuff to You
  • Why Infor’s IDF Is Important for Customer Innovation
  • Four Hundred Monitor, May 25
  • IBM i PTF Guide, Volume 24, Number 21
  • How Committed Is Big Blue To The IBM Cloud?
  • Immutable Copies Are Only As Good As Your Validation
  • Guru: IBM i *USRPRF Security
  • ERP Transitions Loom for SAP on IBM i Customers
  • Inflation Pumps Up Global IT Spending, Supply Chain Deflates It

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.