SQL/MP Programming Manual for COBOL

Host Variables
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
2-13
Retrieving Rows With Null Values
...
0150-SELECT.
EXEC SQL FETCH GET-PRODNUM INTO
:PRODNUM, :DATE-SHIPPED INDICATOR :SHIP-IND
END-EXEC.
* NonStop SQL/MP sets SHIP-IND to -1 if the column
* contained a null value in the selected row.
IF SHIP-IND = -1 THEN MOVE "NULL" TO VALUE-DISPLAY
ELSE MOVE DATE-SHIPPED TO VALUE-DISPLAY.
IF SQLCODE = 0 DISPLAY PRODNUM " " VALUE-DISPLAY.
...
Retrieving Rows With Null Values
You use an indicator variable to insert null values into a database or to test for a null
value after you retrieve a row. However, you cannot use an indicator variable set to -1
in a WHERE clause to retrieve a row that contains a null value. If you use an indicator
variable set to -1 in a WHERE clause, SQL/MP does not find the row and returns an
SQLCODE of 100, even if a column actually contains a null value.
To retrieve a row that contains a null value, use the NULL predicate in the WHERE
clause. For example, to retrieve rows that have null values from the EMPLOYEE table
using a cursor, specify the NULL predicate in the WHERE clause in the associated
SELECT statement when you declare the cursor:
* Declare a cursor to find rows with null salaries.
EXEC SQL DECLARE GET-NULL-SALARY CURSOR FOR
SELECT EMPNUM, FIRST-NAME, LAST-NAME,
DEPTNUM, JOBCODE, SALARY
FROM =EMPLOYEE
WHERE SALARY IS NULL
END-EXEC.
...
PROCEDURE DIVISION.
100-MAIN.
...
EXEC SQL OPEN GET-NULL-SALARY END-EXEC.
PERFORM 200-FETCH-NULL UNTIL SQLCODE OF SQLCA = 100.
EXEC SQL CLOSE GET-NULL-SALARY END-EXEC.
...
200-FETCH-NULL.
EXEC SQL FETCH GET-NULL-SALARY INTO
:EMPNUM OF EMPLOYEE-RECORD,
:FIRST-NAME OF EMPLOYEE-RECORD
:LAST-NAME OF EMPLOYEE-RECORD
:DEPTNUM OF EMPLOYEE-RECORD
:JOBCODE OF EMPLOYEE-RECORD
:SALARY OF EMPLOYEE-RECORD
END-EXEC.
* Process the row that contains the null salary.
...