Easytrieve NULL

NULL Means No Known Value

A database must distinguish “the employee has no phone value recorded” from “the recorded phone is an empty string,” “the phone is all blanks,” and “the phone is zero.” NULL provides that distinction. The underlying data bytes are not sufficient to identify it; an indicator carries the status. Application logic must test NULL before using the data area, because bytes left there can be stale or irrelevant when the indicator says NULL.

SQL INCLUDE NULLABLE

The SQL INCLUDE statement can define fields from database declarations. Adding NULLABLE tells Easytrieve to generate default indicator fields for columns that allow NULL. The indicator is a two-byte binary field that precedes the data portion in storage. Easytrieve uses it automatically whenever the associated nullable field is referenced. Because the generated indicator cannot be directly referenced, use IF FIELD NULL for routine checks.

1
2
3
4
5
6
7
8
9
SQL INCLUDE SQLCA
SQL INCLUDE PERSONNEL NULLABLE
 
JOB INPUT SQL NAME(READ-EMP)
  IF EMPPHONE NULL
     DISPLAY 'PHONE NUMBER IS UNKNOWN'
  ELSE
     DISPLAY EMPPHONE
  END-IF

Exact SQL INCLUDE naming depends on the database declaration and generated field names in your installation. The important point is that the nullable data field—not the hidden default indicator name—is the subject of IF NULL.

IF FIELD NULL and NOT NULL

NULL is one of the documented field-class condition objects. Syntax is IF field-name [NOT] NULL. IF DEPTNAME NULL runs when no known department name was returned. IF DEPTNAME NOT NULL runs when the indicator says the data area contains a known value, even if that known value consists entirely of spaces. Add a SPACES test when both states must be distinguished.

1
2
3
4
5
6
7
8
9
IF DEPTNAME NULL
   DISPLAY-NAME = 'UNKNOWN DEPARTMENT'
ELSE
   IF DEPTNAME SPACES
      DISPLAY-NAME = 'BLANK DEPARTMENT'
   ELSE
      DISPLAY-NAME = DEPTNAME
   END-IF
END-IF

MOVE NULL

MOVE NULL TO nullable-field marks the receiver NULL. It does not simply fill its visible data bytes with zero or spaces. A non-nullable receiver cannot represent that state, so Broadcom documents a runtime error when NULL is moved to a non-nullable field. Validate field definitions and SQL metadata before executing nullable assignments.

1
2
3
4
5
IF PHONE-SUPPLIED = 'N'
   MOVE NULL TO EMPPHONE
ELSE
   EMPPHONE = INPUT-PHONE
END-IF

In the ELSE branch, assigning a normal value sets the receiving nullable field to NOT NULL. Its indicator becomes zero under the documented assignment rules.

Moving SPACES or ZERO Makes a Field NOT NULL

MOVE SPACES and MOVE ZERO are not substitutes for MOVE NULL. Broadcom says moving spaces or zeros to a nullable receiving field sets it to NOT NULL. This lets the program record a known blank or known zero. Choose based on business meaning. Replacing every NULL salary with zero may falsely state that an employee earned nothing, while NULL may mean payroll has not supplied the amount.

Manual Null Indicator Variables

Native SQL statements and automatic retrieval without a generated nullable file can use manual indicators. Define the indicator as two-byte quantitative binary: 2 B 0. Put it after the corresponding host variable in the INTO clause. A negative indicator means the returned column is NULL. Zero normally means the value is present. Some DBMS products use positive indicator values to communicate truncation or length information, so consult the database-specific guide before interpreting every nonnegative value as identical.

1
2
3
4
5
6
7
8
9
10
11
12
13
DEFINE EMPNAME    W 20 A
DEFINE EMPPHONE   W  3 P 0
DEFINE NULLPHONE  W  2 B 0
 
JOB INPUT SQL NAME(READ-PERSONNEL)
  SELECT EMPNAME, EMPPHONE
    INTO :EMPNAME, :EMPPHONE :NULLPHONE
    FROM PERSONNEL
 
  IF NULLPHONE LT 0
     EMPPHONE = 0
     DISPLAY 'PHONE WAS NULL'
  END-IF

This example intentionally converts NULL to zero for a report after recording the state. In a production system, a display label or separate missing-value flag is often more honest than silently replacing unknown data with zero.

Automatic Indicators vs Manual Indicators

NULL in Assignment Expressions

Nullable assignments propagate state according to source and receiver definitions. Assigning a known value to a nullable receiver marks it NOT NULL. Assigning a NULL nullable source to a compatible nullable receiver preserves null status. Assigning NULL to a receiver that cannot hold NULL produces a runtime error. Check NULL before moving into legacy file fields that have no indicator.

NULL in Arithmetic

SQL uses three-valued logic, but Easytrieve arithmetic does not automatically turn NULL into zero. Broadcom's Assignments and Moves rules state that an arithmetic expression with any NULL operand causes a runtime error. Guard every nullable quantity before addition, subtraction, multiplication, division, or SUM preparation.

1
2
3
4
5
6
7
IF BONUS NULL
   DISPLAY-BONUS = 0
   BONUS-MISSING = 'Y'
ELSE
   DISPLAY-BONUS = BONUS
   TOTAL-PAY = BASE-PAY + BONUS
END-IF

The flag preserves the fact that zero was substituted for display. The total calculation runs only when BONUS is known.

NULL in Reports

Reports should expose missing data rather than letting undefined bytes print. Derive a non-null display field and a readable label before PRINT. For numeric columns, a separate alphabetic status such as UNKNOWN may be clearer than printing 0.00. If business rules require zero substitution, explain it in the report title, footnote, or metadata.

1
2
3
4
5
6
7
8
9
DEFINE PHONE-DISPLAY W 15 A
 
IF EMPPHONE NULL
   PHONE-DISPLAY = 'NOT PROVIDED'
ELSE
   PHONE-DISPLAY = EMPPHONE
END-IF
 
PRINT PERSONNEL-RPT

NULL During INSERT and UPDATE

Before an SQL INSERT or UPDATE, MOVE NULL to nullable host fields that should have no value. Assign ordinary content when the column should be NOT NULL. Do not send spaces as a workaround unless a known blank value is the intended database state. Database NOT NULL constraints still apply independently of Easytrieve field definitions.

NULL and VARYING Fields

A VARYING field has length information and data. A zero-length string is still a known, NOT NULL value unless the null indicator says otherwise. That gives three potentially distinct cases: NULL, zero-length string, and a fixed or varying string containing spaces. Database and Easytrieve release behavior should be tested when an application depends on empty-string distinctions.

Data Conversion and Extracts

Flat files have no automatic SQL null indicator unless the layout defines one. When exporting database rows, decide how to encode NULL: a separate flag, a documented literal, an empty delimited column, or another agreed representation. Never assume spaces preserve NULL semantics. The receiving program must know how to reverse the encoding.

Version and Database Considerations

IF NULL, MOVE NULL, SQL INCLUDE NULLABLE, and manual two-byte indicators are documented in Broadcom Easytrieve Report Generator 11.6. Indicator details beyond negative-means-NULL can differ by DBMS, especially truncation handling. Confirm DB2, SQL/DS, IDMS SQL, or other database behavior used by your installation. Generated SQL INCLUDE layouts should be reviewed again after database schema changes.

Common NULL Mistakes

1. Treating NULL as spaces or numeric zero.
2. Using a nullable value in arithmetic without testing it.
3. Moving NULL to a non-nullable work or file field.
4. Pairing a manual indicator with the wrong host variable.
5. Assuming zero-length VARYING data is NULL.
6. Replacing unknown amounts with zero without preserving the distinction.
7. Testing raw data bytes instead of the null indicator.

Explain It Like I'm Five

Imagine a box for a phone number and a small sign beside it. Spaces mean someone knows the phone entry is blank. Zero means someone knows the number recorded is zero. NULL means the sign says “we do not know the value,” so whatever old marks remain inside the box should not be trusted. Easytrieve reads the sign before it uses the box.

Exercises

1. Write IF logic that distinguishes NULL, SPACES, and a populated phone field.
2. Define a manual 2 B 0 indicator and pair it with a host variable in SELECT INTO.
3. Guard a bonus calculation so a NULL operand cannot reach arithmetic.
4. Set a nullable field first to NULL and then to a known blank value; explain the indicators.
5. Design a flat-file representation that preserves NULL separately from zero and spaces.
6. Create a readable report field for a nullable numeric database column.