RPGLE Null Values in Fields - ALWNULL in iSeries(AS400)

How to handle file with fields that allow null values in RPGLE or SQLRPGLE

What are null capable fields? If you are defining a Physical file then you use the attribute ALWNULL in the specification to specify that the field is capable of taking null values. So the default in DDS if don't define a field with keyword ALWNULL means they are not null capable. Here is a Physical file sample.

A                                      UNIQUE
A          R MYREC
A            UNIQUEID       9B 0
A            FLD1          10A
A            DATEFLD         L         DATFMT(*ISO)
A            STRING        30A         ALWNULL
A                                      
A          K UNIQUEID

In case of creating tables using SQL its the other way around. If you don't specify anything then it means the field is null capable. Here is an example

CREATE TABLE MyTable (UniqueID  INT NOT NULL, FLD1 CHAR(10) NOT NULL, 
DateFLD DATE NOT NULL, String CHAR(30), Unique (UniqueID))

How to Ignore null capable files in RPGLE

That's easy to do if you don't care about the field if it's null or not. All you have to do is use the compile option ALWNULL either in H-specs or during compiling and the program will set the null field to its default value when reading the file. So if the null field is character then it will come as blanks.
Use *YES or *INPUTONLY as values for ALWNULL for reading a file with null values.

How to Ignore null capable files in SQLRPGLE

You have to use the SQL coalesce function. COALESCE ( expression [ ,...n ] )

Here is an sample code

d $String         s             30a           
d $id             s             10I 0 inz(1)  
                                              
 /free                                        
                                              
  //Set SQL options                           
  exec sql                                    
   SET OPTION                                 
       commit=*none,                          
       datfmt=*iso;                           
                                              
  exec sql                                    
    Select Coalesce(String,' ') into :$String 
           from das/mytable                   
           where UniqueId = :$id;             
                                              
    if sqlcod = 0;                            
    dsply $string; //do something             
    endif;                                    
    if sqlcod <> 0;                           
    dsply sqlcod; //do something              
    endif;                                    
                                              
                
  *inlr = *on;  
                
 /end-free  

So if the String has value it will take that or if it is null then it will make it blanks.

But if you really want to figure out whether the field has null value or not or if you want to move null value to a field then you must define the ALWNULL option to *USRCTL.

%NULLIND(fieldname)

The %NULLIND built-in function can be used to query or set the null indicator for null-capable fields. This built-in function can only be used if the ALWNULL(*USRCTL) keyword is specified on a control specification or as a command parameter. The fieldname can be a null-capable array element, data structure, stand-alone field, subfield, or multiple occurrence data structure.

%NULLIND can only be used in expressions in extended factor 2.

• When used on the right-hand side of an expression, this function returns the setting of the null indicator for the null-capable field. The setting can be *ON or *OFF.
• When used on the left-hand side of an expression, this function can be used to set the null indicator for null-capable fields to *ON or *OFF. The content of a null-capable field remains unchanged. 

 /FREE
    if  %nullind (String);
        // field is null
    endif;
 // Set the null indicator for a null-capable field.
    %nullind(String) = *ON;
    %nullind(String) = *OFF;
 /END-FREE

How to Find null capable files in SQLRPGLE

There are two ways to do it. One is using the above mentioned COALESCE function and the second is using the NULL indicator.

• Sample code using COALESCE

d $String         s             30a               
d $id             s             10I 0 inz(2)      
                                                  
 /free                                            
                                                  
  //Set SQL options                               
  exec sql                                        
   SET OPTION                                     
       commit=*none,                              
       datfmt=*iso;                               
                                                  
  exec sql                                        
    Select Coalesce(String,'@@@@') into :$String  
           from das/mytable                       
           where UniqueId = :$id;                 
                                                  
    if sqlcod = 0;                                
    dsply $string; //do something                 
    endif;                                        
    if sqlcod <> 0;                               
    dsply sqlcod; //do something                  
    endif;                                        
                                                  
  *inlr = *on; 
               
 /end-free    

So if the String has value it will take that or if it is null then it will make it @@@@. Just make sure the value that you are using is not in the column so you can identify which ones are null.

• Sample code using NULL Indicator

d $String         s             30a          
d $NullInd        s              5I 0        
d $Null           S              5I 0 Inz(-1) 
d $id             s             10I 0 inz(2) 
                                             
 /free                                       
                                             
  //Set SQL options                          
  exec sql                                   
   SET OPTION                                
       commit=*none,                         
       datfmt=*iso;                          
                                             
  exec sql                                   
    Select String into :$String:$NullInd     
           from das/mytable                  
           where UniqueId = :$id;            
                                             
    if sqlcod = 0;                           
    dsply $string;  //do something           
    dsply $NullInd; //do something           
    if $NullInd = $Null;                     
       dsply 'Yes it is NULL now';            
    endif;                       
    endif;                       
    if sqlcod <> 0;              
    dsply sqlcod; //do something 
    endif;                       
                                 
  *inlr = *on;                   
                                 
 /end-free