NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
L-38
Considerations—LOAD
Compile-time errors (such as references to nonexistent fields or mapping between
incompatible source and target fields) always terminate the LOAD operation. Data-
loading errors (such as violations of integrity constraints and source data that
exceeds the length of the target field) can be permitted or restricted with the
ALLOWERRORS option.
•
Using the FIRST KEY option
The FIRST KEY option specifies the starting point in the input table or file for a
LOAD operation. Unlike the FIRST KEY clause in the CREATE TABLE and
ALTER TABLE statements, FIRST KEY does not interpret the key value or values
in terms of the key's data type, nor does it match them with specific columns.
Instead, it accepts a series of data values and matches them byte-for-byte against the
key.
Therefore, you must make sure the value or values you supply represent the desired
data in the actual key in internal SQL format. Additionally, you should supply a
complete value for each column. You can specify only a portion of the last column
in the FIRST KEY specification, but SQL uses those bytes as the leftmost bytes,
which might produce an unintended result.
Following are guidelines:
°
You can specify string literals or numeric literals. A string literal occupies as
many bytes as there are characters in the literal. Each numeric literal occupies
one byte.
°
To specify a value for a CHAR column, enclose the value in quotes. For
example:
FIRST KEY ("value")
To include nondisplayable characters, use the equivalent ASCII numeric form.
The following example specifies the BEL control character, with a numeric
value of 7 as the fifth byte in an 8-byte key value:
FIRST KEY ("asdf",7,"jkl")
Be sure to supply enough bytes to fill the column. Pad the string with blanks or
the appropriate pad character for your application.
°
To specify a value for a VARCHAR column, treat the value as a CHAR value
and pad the value with blanks (or the appropriate pad character for your
application) to the maximum size of the VARCHAR column.
°
To specify a value for a binary column (such as SMALLINT, INTEGER, or
LARGEINT), specify as many numeric literals as there are bytes in the column.
For example, each SMALLINT column is two bytes long, so specify two
numbers for each SMALLINT column. To determine the size of your target key
value, look at the binary representation of the key value and determine the
number of 8-bit groups (bytes) in the value.
One way to figure out byte values for positive values is to divide repeatedly by
256 until you reach a quotient of zero, and take the remainders in reverse order.