Generally, not only in the case of NULLS resulting from
outer joins, but also for NULLS fetched from the database,
when using a tool that implements EMBEDDED SELECT, then
you should use indicator variables to detect the NULLS
For example, many years ago, when I used Oracle Pro*Cobol,
then the technique was as follows:
1. use a set of host variables for collecting the fetched
2. use another set of host NUMERIC variables as indicator
variables (same number as the above host variables)
3. before performing the FETCH (after opening the SELECT
cursor, a.s.o.), we always initialized all the host
variables above (numeric host variables to ZERO, string
host variables to SPACES).
Usually, the host variables were always ARRAYS of size
1000 for better performance (less FETCH-es performed).
The FETCH itself looked like this:
( with the proper number of INTO variables ).
That is, each host return variable is followed immediately
by an indicator variable (separated by a second colon).
After the FETCH, if the fetched value was a NULL value,
then the host return variable remained as it was initialized before the FETCH, but the indicator host
variable contained -1.
This was the ONLY indication that the fetched value was
in fact a NULL and not a ZERO or a SPACES value.
In the specific case of Oracle Pro*Cobol, using the host
indicator variables was NOT MANDATORY, except for the case
that you wanted to be able to recognize the NULLS.
If this was not required by the program’s logic, then the
host indicator variables could been left off, without
causing any error.
Maybe this is not the case with other tools using embedded
The usage of the indicator variables is the same for
INSERT and UPDATE statements, when you intend to INSERT
a NULL into the database, for example:
INSERT INTO my_table (col1)
If my_indicator_var1 is set to -1, then a NULL will be
inserted into the database.
Hope this information could help.