Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL Setting Null Indicator In a Field during UPDATE or INSERT
has no ratings.
Published: 22 Nov 2011
Revised: 05 Mar 2014 - 1177 days ago
Last viewed on: 25 May 2017 (7719 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL Setting Null Indicator In a Field during UPDATE or INSERT Published by: Bob Cozzi on 22 Nov 2011 view comments

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 

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS