DB2 Null Statement

Scenario1 - the program retrieves an employee's name from the Employees table. If the Emp_Name column is null, the null indicator will be set to -1.

...
MOVE 101 TO EMP-ID.

EXEC SQL
    SELECT emp_name
      INTO :EMP-NAME 
	       :EMP-NAME-NI
      FROM employees
     WHERE emp_id = :EMP-ID
END-EXEC.

IF SQLCODE = 0 THEN
...

• The SELECT statement tries to retrieve the employee name for the given Employee ID.
• The :EMP-NAME-NI checks whether the Emp_Name column has a null value.
  • If EMP-NAME-NI is -1: The column value is null, and the message 'Employee Name is NULL.' is displayed.
  • If EMP-NAME-NI is 0: The column has a valid value, and the employee name is displayed.

Scenario2 - When inserting data, if you want to set a column to NULL, you need to use the null indicator.

...
MOVE 101 TO WS-EMP-ID.
MOVE -1 TO WS-NAME-NULL-IND.   * Indicating the name is NULL

EXEC SQL
    INSERT 
	   INTO Employees 
	(emp_id, emp_name)
    VALUES (:EMP-ID, 
	        :EMP-NAME 
			:EMP-NAME-NI)
END-EXEC.

IF SQLCODE = 0 THEN
...

• If the null indicator is -1, the value of Emp_Name will be inserted as NULL.
• If the null indicator is 0, the value in EMP-NAME will be inserted.