Creating New SQL Data Tables › Assigning Null Values to an SQL Column

Assigning Null Values to an SQL Column

The technique for assigning null values to an SQL column is quite simple: set the column null indicator to minus one (-1).

Consider the example of a phonelist. For the WORK_PHONE field, you could use blanks to mean "This person has no work phone" and null to mean "He may have a work phone, but we don't know the number".

On the sequential input file, null values are represented by a question mark (?) in the WORK_PHONE field. The example below reads the input file, converts question marks to null values, and creates a new SQL table containing the phonelist:

Code Example

 col. 2
 ▼
 IN 80
 REC  FIRST_NAME  1  12
 REC  LAST_NAME  13  15
 REC  IN_HOMEPH  28  12
 REC  IN_WORKPH  40  12
 010  HOME_PHONE '            '
 010  WORK_PHONE '            '
 010  SPACES     '            '
 010  HOME_PHONE_NULL_IND  0
 010  WORK_PHONE_NULL_IND  0
 01OUT SQLTABLE=PHONELIST  TYPE=CREATE
*      DICTIONARY=TSTDICT  SCHEMA=DEMO
 0151*010  FIRST_NAME      NOT NULL  WITH DEFAULT
 0151*020  LAST_NAME       NOT NULL  WITH DEFAULT
 0151*030  HOME_PHONE
 0151*031  HOME_PHONE_NULL_IND  FB SZ=4
 0151*040  WORK_PHONE
 0151*041  WORK_PHONE_NULL_IND  FB SZ=4
 017    IF IN_HOMEPH = '?'  100
 017    MOVE IN_HOMEPH TO HOME_PHONE         $ Not Null - Valid Number
 017    MOVE 0         TO HOME_PHONE_NULL_IND
 017    B  200
 017100 MOVE SPACES    TO HOME_PHONE         $ Assign Null Value
 017    MOVE -1        TO HOME_PHONE_NULL_IND
 017$
 017200 IF IN_WORKPH = '?'  300
 017    MOVE IN_WORKPH TO WORK_PHONE         $ Not Null - Valid Number
 017    MOVE 0         TO WORK_PHONE_NULL_IND
 017    B  400
 017300 MOVE SPACES    TO WORK_PHONE         $ Assign Null Value
 017    MOVE -1        TO WORK_PHONE_NULL_IND
 017400 TAKE