When using native RPG I/O (read, chain, update, etc.) the RPG %NULLIND built-in function may be used to test a field for NULL INDICATOR in a database record or a data structure subfield based on a database file record (i.e., defined with LIKEREC). But to read, write or update a database record using SQL and SET the NULL INDICATOR (or test for it on a FETCH) SQL uses a its own syntax; %NULLIND does not apply to data read by SQL.

The syntax for the way you test for for NULL is different depending on whether a SELECT INTO or FETCH INTO is being used vs an INSERT or UPDATE statement.

NULL INDICATOR on SQL FETCH or SELECT INTO Statement

SELECT ordDate, ShipDate, custNo
 INTO  :orderDate INDICATOR :iOrder,
       :shipDate INDICATOR :iShip,
       :cstNbr
  FROM ORDERS
  WHERE ORDNBR = :order;

The iORDER and iSHIP fields must be defined as 5i0 values (2-byte integers) in RPG. The SQL keyword "INDICATOR" is between the actual host variable name and the 5i0 "null indicator" variable. The keyword "INDICATOR" is optional, but helps improve clarity/readability.

Upon a successful SELECT or FETCH operation, the iShip and iOrder variables will be set to one of the following:

• 0 = NULL INDICATOR is *OFF;
• -1 = NULL INDICATOR is *ON;
• -2 = The field isn't NULL but its content is bogus (e.g. invalid date)

After the SELECT or FETCH, check the value of the indicator variable ("indicator" in SQL speak not RPG nomenclature). If the INDICATOR is -1 the field's null indicator is ON; if the indicator variable is 0, the field's null indicator is OFF.

When the NULL indicator is on, the value in the field should be ignored.

When the NULL indicator is off, the value in the field is good and may be used.

NULL INDICATOR on SQL UPDATE/WRITE Operations

UPDATE ORDERS SET shipDate = :shipDate:iShip, 
                  ordDate  = :ordDate:iOrder
       WHERE ORDNBR = :order;

 The NULL indicator syntax on an UPDATE or INSERT operation is as follows:

 field = :variable:indyVar

Where field is the field name in the file, variable is the host variable containing the new value for the field, and indyVar is a 5i0 variable containing -1 or 0 to set the database field's NULL Indicator on or off, respectively. Here is the RPG IV declaration for an SQL Indicator Variable:

.....D iNullIndy       S              5i 0