NSSQL : Nullable columns

Nullable columns in search-conditions Sometimes the use of nullable columns may lead to unexpected results. Note that a row is not selected when the expression in the search-condition evaluates to NULL. Example: Table definition: -- Definition of table    (      ACT_NR                           PIC 9(2) COMP NO DEFAULT NOT NULL   , KANTOOR                        PIC S9(4) COMP NO DEFAULT NOT NULL   , LOCATIE                           PIC 9(2) COMP NO DEFAULT NOT NULL   , VP                                      PIC S9(4) COMP NO DEFAULT   ) Actual data select * from rekening ACT_NR  KANTOOR  LOCATIE  VP    ------        -------          -------    ------       1              1                  1         11       6              1                  1          ? --- 2 row(s) selected. The following query might not deliver the result you expect select * from rekening where vp <> 55; REK_NR  KANTOOR  LOCATIE  VP   ------           -------        -------    ------      1                 1                1         11 --- 1 row(s) selected. The row with a NULL value in the vp-column is not selected. NULL is not different  from 55 but also not equal to 55. It is simply unknown. If the row with the NULL-value has to be selected also, then following syntax has to be used: select * from rekening where vp <> 55 or vp is NULL; REK_NR  KANTOOR  LOCATIE  VP   ------          -------         -------    ------       1               1                1         11       6               1                1          ? --- 2 row(s) selected. Nullable columns in COBOL Another issue to watch carefully is the use of a nullable column in a search-condition in a COBOL program. If the value of a nullable column has to be compared to a host-variable in a search-condition, and the host-variable must be able to contain NULL, then the following construction has to be used: Select ……………   from ……………………   where <other selection criteria>   and (column_name = :valu of column-name and :indicator of column-name > -1            or column_name is null                          and :indicator of column-name <  0) Do not use the following construction (SQL does not recognize the indicator): Select ……………   from …………………… where <other selection criteria>    and (column_name = :valu      of column-name             indicator :indicator of column-name) Suppose a table TEMP. The invoke under SQLCI gives the following result: invoke testtab; -- Definition of table -- Definition current at 16:19:11 - 09/06/01   (     TEMP-ID                        PIC X(5) NO DEFAULT NOT NULL   , OMS                            PIC X(80) NO DEFAULT   , OMS-LANG                       VARCHAR(255) NO DEFAULT NOT NULL   ) Invoke in a COBOL program results in: invoke temp as temp 01 TEMP.     02 TEMP-ID                          PIC X(5).     02 OMS-I                            PIC S9(4) COMP.     02 OMS                              PIC X(80).     02 OMS-LANG.        03 LEN                           PIC S9(4) COMP.        03 VAL                           PIC X(255). Using this type of invoke in COBOL the nullable column cannot be treated as one object. It results in two host-variables on the same level (OMS-I en OMS). This structure also differs from the host-variable structure for a varchar column. A host-variable for a varchar has two levels: the column-name on the highest level and the “LEN” and  “VAL” fields at the lower level. Rather use the next syntax in COBOL programs: invoke temp as temp null structure; Result: 01 TEMP.     02 TEMP-ID                          PIC X(5).     02 OMS.        03 INDICATOR                     PIC S9(4) COMP.        03 VALU                          PIC X(80).     02 OMS-LANG.        03 LEN                           PIC S9(4) COMP.        03 VAL                           PIC X(255).  Now the host-variable for the nullable column has two levels, with at the lowest level two fields with the names “indicator” en “valu”.