A missing value for a field in a relation has no value associated with
it. The missing value is an attribute of a field rather than a value
stored in a field.
The null flag refers to the information that Oracle Rdb maintains about a
field to determine whether or not it is null, or missing. Each field in
the database has information associated with it using the null flag to
mark the field as either missing or not missing. Value expressions can
evaluate to a value or to missing. Conditional expressions can evaluate
to true, false, or missing. Missing information also affects the
evaluation of statistical functions, sorting, and projections.
If the source of the assignment is a database field and that database
field has a missing value defined for it, then its missing value is
used. Otherwise, the missing value is determined by the data type. For
fixed-length strings, the string is blank filled. For variable-length
strings, the length is set to zero. All numeric data types are set to
zero. For the DATE data type, the missing value of the VMS zero date is
used. This represents the date 17-NOV-1858 00:00:00.00.
When you assign a value to a database field, the null flag for that
field is set according to the value in the assignment statement. If the
value of the assignment is the missing value defined for that field, the
null flag is set for that field. Otherwise, the null flag is not set.
If no missing value is defined, the null flag for that field is not set.
When you define a field, choose a missing value for that field that will
never occur as an actual value for the field. If you change the
definition of the missing value for a field, you change the incoming
value that sets the null flag and you change the value produced when
reading a field with the null flag set.
Oracle Rdb evaluates the expression RDB$MISSING to determine the missing
value for a field. There are two ways to declare that a field is missing:
o Omit the field in the STORE statement.
When you omit the field from the STORE statement, Oracle Rdb VMS
marks the field as null.
o Explicitly store the value associated with RDB$MISSING for the field,
or use RDB$MISSING to signal the field's value to Oracle Rdb. Oracle
Rdb does not actually store what you specify; rather, it stores nothing
and marks the field's value as missing or null.
If you know the missing value for a field, you can include this value in
the STORE or MODIFY statement. For example, assume you want to make the
ADDRESS_DATA field missing for a particular employee. The definition for
ADDRESS_DATA is as follows:
DEFINE FIELD ADDRESS_DATA
DATA_TYPE IS TEXT SIZE IS 20
MISSING_VALUE IS 'Not available'.
To make the field missing, you use a MODIFY statement:
FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = "00175"
MODIFY E USING
E.ADDRESS_DATA = 'Not available'
END_MODIFY
END_FOR
When this statement executes, Oracle Rdb marks the field as null; and
when you retrieve the field, Oracle Rdb returns the value "Not
available", the missing value. Note, however, that to find all the
records in which the ADDRESS_DATA field is marked as null, use the
MISSING operator in a conditional expression, as in the following
example:
FOR E IN EMPLOYEES WITH E.ADDRESS_DATA MISSING
PRINT E.*
END_FOR
If instead of the MISSING operator, you use the missing value in the
conditional expression, Oracle Rdb displays nothing. The following query
does not find records in which the ADDRESS_DATA field is marked as null:
FOR E IN EMPLOYEES WITH E.ADDRESS_DATA = "Not available"
PRINT E.*
END_FOR
If you want to make a field missing and you do not know the literal
missing value, you can use the expression RDB$MISSING, which returns the
missing value. For example, you might want to write general update
programs that do not depend on a particular missing value. You can use
RDB$MISSING instead of an explicit value in a STORE or MODIFY statement.
________________________Note ________________________
In order to use RDB$MISSING, you must have defined a
missing value explicitly for the field in the DEFINE
FIELD statement. If you have not, RDB$MISSING is
undefined for that field.
_____________________________________________________
You can define a missing value that is longer than the length specified
for the field in the field definition. However, when the missing value
is displayed, it will be truncated to the length specified for the field
in the field definition. In the following example, the missing value
"Long" in the field SEX_FIELD is truncated to one character (the length
specified for the field in the definition of SEX_FIELD).
RDO> !
RDO> ! Define field SEX_FIELD:
RDO> !
RDO> DEFINE FIELD SEX_FIELD
cont> DATATYPE IS TEXT 1
cont> MISSING_VALUE "Long".
RDO> !
RDO> ! Define relation TEST_RELATION:
RDO> !
RDO> DEFINE RELATION TEST_RELATION.
cont> SEX_FIELD.
cont> EMPLOYEE_ID BASED ON ID_NUMBER.
cont> END RELATION.
RDO> !
RDO> ! Store a single record in TEST_RELATION, providing a value
RDO> ! for EMPLOYEE_ID, but not for SEX_FIELD. This creates a
RDO> ! missing value for SEX_FIELD in the record:
RDO> !
RDO> STORE TR IN TEST_RELATION USING
cont> TR.EMPLOYEE_ID = "00175";
cont> END_STORE
RDO> !
RDO> ! The missing value in SEX_FIELD appears as one character
RDO> ! when the record is displayed:
RDO> !
RDO> FOR TR IN TEST_RELATION
cont> PRINT TR.*
cont> END_FOR
SEX_FIELD EMPLOYEE_ID
L 00175
1 – Format
(B)0[mmissing-value =
q> [4mRDB$MISSING[m q> ( qwq> context-var . field-name qqqqqqqqqqwq ) q>
tq> rel-name . field-name qqqqqqqqqqqqqu
mq> db_handle . rel-name . field-name qj
2 – Usage Notes
o The default value for a field ("Rdb default" in displays by the
SQL statement SHOW TABLE table-name) is not the same as the missing
value that you can specify with RDO. If you do not specify a value
for a field (column) with a default value, the default value is
actually stored in the database. This is true whether you are using
RDO or SQL.
An RDO missing value is not actually stored in the database. If you
use RDO to specify a missing value for a field, that missing value
is displayed by RDO when the field has no value stored and the
internal null flag is set. SQL does not recognize any missing value
specified by RDO; if the field has no value stored and the null flag
is set, then SQL displays "NULL" for the column, regardless of
whether you specified any missing value with RDO.
One implication of the way in which Oracle Rdb handles default values
is that if you change the default value for a column, it has no
effect on any existing data in the database; that is, rows stored
with columns containing the "old" default values are not changed.
By contrast, changing the missing value does change what is displayed
by RDO-based applications for columns that have no value stored and
that have the null flag set.
o The value of RDB$MISSING is set at compile time. If the missing
value for a field is changed, then sources that contain references
to RDB$MISSING for that field still use the old missing value. To
correct this problem, all sources that contain a reference to
RDB$MISSING on the field must be recompiled.
3 – Examples
Example 1
The following example uses RDB$MISSING in a STORE statement:
.
.
.
*
* If the date in the input file contains zeros,
* use the GET statement with RDB$MISSING to retrieve
* the missing value and assign it to a variable.
*
IF END-DATE = '000000' THEN
&RDB& GET END-DATE = RDB$MISSING(SALARY_HISTORY.SALARY_END)
&RDB& END_GET
END-IF.
*
* Store the field values in the relation.
* If the date in the input file contained zeros,
* the missing value is passed to Oracle Rdb, and
* the SALARY_END field is marked as missing.
*
&RDB& STORE S IN SALARY_HISTORY USING
&RDB& S.EMPLOYEE_ID = EMP-ID;
&RDB& S.SALARY_START = START-DATE;
&RDB& S.SALARY_END = END-DATE;
&RDB& S.SALARY_AMOUNT = SALARY;
&RDB& END_STORE
This program fragment shows how your program might test for a missing
field in an input file and make the field missing in the database. This
program assumes that a missing value has been defined for the field. The
program does not need to know what the missing value is, and the
program needs only one STORE statement.