SQL Indicator Variables in Embedded SQL for RPG on IBM i

When working with DB2 on IBM i, handling NULL values properly is essential, especially when you retrieve data using embedded SQL in RPG. In this post, I’ll show you a simple and practical way to check if a column is NULL using what’s called an SQL Indicator Variable.

The Scenario

Let’s say you have a customer file called CLANA00F with the following fields:

Field	Type	Length	Description
CLANN0	CHAR(1)	1	Customer status
CLCCL0	DEC(7,0)	7	Customer ID
CLCIB0	CHAR(3)	3	Customer type
CLNOM0	CHAR(50)	50	Customer name
CLSTS0	CHAR(1)	1	Customer status

Now, imagine that CLSTS0 is nullable (i.e., it can contain NULL), and you want to find out whether it has a value or not.

The RPG Example

**free
ctl-opt dftactgrp(*no) actgrp(*caller);
// ****************************************************************
// * Autore.......: ALDO SUCCI                                    *
// * Descrizione..: 2.1 Embedded SQL in RPG & Error Handling      *
//                  & Indicators (Set 2)                          *
// ****************************************************************
//
// This program receives a Customer ID (ppCCL0) as input,
// performs an embedded SQL query to retrieve the CLSTS0 field
// from the CLANA00F table matching that Customer ID.
//
// It uses an SQL indicator variable (CLSTS1Ind) to check
// if the CLSTS0 field is NULL.
//
// It displays:
// - "No customer found" if the record does not exist
// - "Cust status is NULL" if the CLSTS0 field is NULL
// - Otherwise, it displays the value of the customer status
//
// CLANA00F - Customer File
// COLUMN     | T |  POS. |  POS. | ---FIELD-- | DE | COLUMN
// NAME       | Y |  FROM |    TO | BYTES | LN | CI | DESCRIPTION
//            |   |       |       |       |    |----|
// CLANN0     | A |     1 |     1 |     1 |    |    | Cusr status
// CLCCL0     | S |     2 |     8 |     7 |  7 |    | CustID
// CLCIB0     | A |     9 |    11 |     3 |    |    | Cust type
// CLNOM0     | A |    12 |    61 |    50 |    |    | Cust name
// CLSTS0     | A |    62 |    62 |     1 |    |    | Cust status

dcl-s CLSTS1   char(3);
dcl-s CLSTS1Ind int(5); // SQL Indicator for CLSTS1
dcl-pi NUL_X extpgm('NUL_X');
   ppCCL0 packed(7:0);
end-pi;

exec sql
   select CLSTS0 into :CLSTS1 :CLSTS1Ind
     from CLANA00F
     where CLCCL0 = :ppCCL0;
if SQLCODE = 100;
   dsply 'Nessun cliente trovato.';
elseif CLSTS1Ind = -1;
   dsply 'Cust status is NULL';
   clear CLSTS1; // Not needed here since we're not in a loop,
                 // but keep in mind: if the column is NULL,
                 // the indicator is set to -1 while the host
                 // variable keeps its previous value from the
                 // last FETCH/SELECT.
else;
   dsply ('Cust status: ' + %trim(CLSTS1));
endif;
*inlr = *on;

What is an SQL Indicator Variable?

An indicator variable is a companion to your data variable. It tells you if the value retrieved from the database was NULL. Here’s what the values mean:

Indicator	Meaning
0	The column has a value
-1	The column is NULL

This lets you safely handle optional or missing data in your programs without crashing or making wrong assumptions.

How to Run It

You can test the program by calling it from the command line:

CALL NUL_X PARM(x'0000001F')

Where 0000001F is the customer ID (CLCCL0). If the customer exists and CLSTS0 is NULL, you'll see the appropriate message.

Why This Matters

Using indicator variables might seem like a small detail but it’s a powerful tool for professional IBM i developers:

• It prevents incorrect data assumptions.
• It helps you deal with legacy files that use nullable fields.
• It improves the reliability and readability of your code.

Let’s Connect

Do you use SQL indicator variables in your IBM i applications?
Drop a comment, share your approach, or connect with me on LinkedIn.
Let’s keep modernizing RPG and sharing real-world IBM i solutions!