How to check for Null Indicator in COBOL?

[Mukul Roy]

Hi,

When I select some data from DB2 using SQL in COBOL program and a column in DB2 contains Null then how to capture and test that the column is Null? If you can guide me, that will be helpful.

[Robert Sample]

The method is described in the manual; IIRC you define the column with two 49-level variables and the first is set if the column is NULL and is not set if the column is not NULL while the second variable contains the value if the column is not NULL. Two variables must be used because otherwise there is no way to indicate that a value is NULL.

[enrico-sorichetti]

googling with

cobol sql null column processing examples

will return many many links with the relevant code snippets

[Mukul Roy]

Thanks Enrico, I thought I'll get a better understanding here..

[Anuj Dhawan]

I think I'll go in bit of explanation before answering your question -

SQL, unlike COBOL, supports variables that can contain null (values - the use of word "value" along with NULL is not usually encouraged as NULL itself means the absence of value and it in itself can not be termed as "value", well). Actually, a null "value" means that no entry has been made and usually implies that the value is either unknown or undefined, at the moment. For example, a null value in a joining date column does not mean that the date is undetermined, it means that the date is not known or has not been set yet.

For a column in a DB2 table for which null is allowed, special handling is required in a COBOL program. The program should define a null indicator variable for each column for which nulls are allowed. This null indicator variable should be defined as a binary halfword – PIC S9(4) COMP (why? - well, at the moment treat it as a rule) – and should be listed in the FETCH as an additional host variable immediately after the regular host variable. If the value of this indicator variable is less than zero then the attribute within the table was null.

To answer your question - let's begin with a CREATE table called MYTABLE which contains three fields: COL_A (which is NOT NULL), COL_B (nulls allowed) and COL_C (nulls allowed). We then use INSERT statements to populate the table:

Code: Select all

CREATE TABLE MYTABLE
  ( COL_A  CHAR(2)  NOT NULL,
    COL_B  CHAR(2)          ,
    COL_C  CHAR(2)            ) ;

Code: Select all

INSERT INTO MYTABLE VALUES ('A', 'B', 'C');
INSERT INTO MYTABLE VALUES ('D', 'E', NULL);
INSERT INTO MYTABLE VALUES ('F', NULL, G');
INSERT INTO MYTABLE VALUES ('H', NULL, NULL);
INSERT INTO MYTABLE VALUES ('I', 'J', 'K');

Now below, the indicator variables are coded within the WORKING-STORAGE SECTION as follows (no such variable is coded for COL_A as it was defined as NOT NULL):

Code: Select all

05  COL_B-NULL-INDICATOR    PIC S9(4)   COMP.
05  COL_C-NULL-INDICATOR    PIC S9(4)   COMP.

You can see that the null indicator variable is listed in the FETCH as an additional host variable immediately after the regular host variable as follows:

Code: Select all

EXEC SQL

     FETCH MYTABLE-CURSOR INTO
     :MY-COL_A ,
     :MY-COL_B :COL_B-NULL-INDICATOR,
     :MY-COL_C :COL_C-NULL-INDICATOR
END-EXEC.

If the value retrieved from the table is null, the null indicator variable will contain a value less than zero, so we check the null indicator as follows:

Code: Select all

IF COL_B-NULL-INDICATOR < 0
   MOVE ALL '*' TO DL-COL_B
ELSE
   MOVE MY-COL_B TO DL-COL_B
END-IF

Hope this helps.

[Mukul Roy]

Greatly explained. This helps. Thank you.