The value of an indicator variable tells the status of a row after a query.
01 FILLER. 05 WS-AMOUNT PIC S9(5)V9(2) COMP-3. 05 WS-CUSTNUM PIC X(5).
01 FILLER. 05 AMT-IND PIC S9(4) COMP.
Consider the below SQL.
EXEC SQL SELECT CUST_AMOUNT INTO :WS-AMOUNT:AMT-IND FROM T100.CUST WHERE CUST_ID = :WS-CUSTNUM END-EXEC.
After a query, the indicator variable contains the following:
- 0 - Column is not null
- -1 - Column is null
- -2 - Column is null as result of conversion error
- +length - Full length of column that was truncated to fit a short host variable
Some points to ponder:
- Load -1 to the indicator variable to set a column to a null value, during UPDATE or INSERT of a row.
- If a column is always to be set to a null value, code the NULL keyword for the column in the UPDATE statement’s SET clause or in the INSERT statement’s VALUES clause.
- A column omitted from the row list of an INSERT statement will always be set to a null value, if the column was defined as NOT NULL; otherwise, an error will occur.
- Code a predicate to test for null with the following syntax:
WHERE column name IS [NOT] NULL
The scalar functions, VALUE and COALESCE, are equivalent, and they can be used only in outer joins; each takes a list of multiple parameters and returns the first parameter that is not null.
The following will return either a non-null column value or a literal:
EXEC SQL SELECT ACCT_REG,VALUE(ACCT_A1, ACCT_A2, ‘NO ACCT’) INTO :WS-REGION ,:WS-ACCOUNT FROM REGS.TABLE_EMP WHERE ACCT_REG <> ’65’ END-EXEC.