SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Utilities
HP NonStop SQL/MX Reference Manual—540440-003
5-34
Considerations for import
import and Nullable Columns
Suppose you need to import into a table that allows nullable columns. Follow these
guidelines:
If you specify that the input file is in delimited format:
•
You can specify a null value for a particular column value with a record in the input
file by using two successive field delimiters. In the case of the first column, start the
record with a field delimiter. In the case of the last column, end the record with a
field delimiter just before the row delimiter.
•
If you specify a format file, import ignores the [FIXED WIDTH FORMAT] section,
and ignores any NullValue= line.
•
In the [COLUMN FORMAT] section of the format file, you could specify Y for the
nullDefault_flag portion of the col=field_name,skip,nullDefault_flag line. If you do
this, then on any particular input record:
°
You can specify a null value for that column either by specifying two
successive field delimiters, or by starting the input data specification with the
character “N” or “n”. import ignores any characters after the “N” or “n” and
before the next field delimiter or row delimiter.
°
You can specify that the column be given the default value (for the column) by
starting the input data specification with the character “D” or “d”. import ignores
anything following that and preceding the next field delimiter or row delimiter.
°
If the first character of the input data specification is anything other than “N”,
“n”, “D”, or ‘d’, import ignores the first character. import uses data beginning in
the next character position up to the next field delimiter or row delimiter as the
value for the column.
If you specify that the input file is in fixed format:
•
You can specify a null value for a particular column value by specifying all of the
characters in the fixed-length field as the NullValue character.
•
If you specify a format file with a [FIXED WIDTH FORMAT] section that contains a
NullValue= line, the specified character is taken as the NullValue character.
Otherwise, the NullValue character defaults to the space character (0x20 for
ISO88591 input files or 0x0020 for UCS2 files).
•
In the [COLUMN FORMAT] section of the format file, you could specify Y for the
nullDefault_flag portion of the col=field_name,skip,nullDefault_flag line. If you do
this, then on any particular input record:
°
You can specify a null value for that column either by specifying two
successive field delimiters, or by starting the input data specification with the
character “N” or “n”. import ignores any characters after the '“N” or “n” and
before the end of the input field.