Handling NULL in DB2

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.  Host variables

    05  WS-CUSTNUM        PIC  X(5).

01  FILLER.

    05  AMT-IND           PIC S9(4) COMP.         Indicator variable

    EXEC SQL

      SELECT CUST_AMOUNT

        INTO :WS-AMOUNT:AMT-IND       No intervening space

        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

 

Load -1 to the indicator variable to set a column to a null value, during UPDATE or INSERTof 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.