NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
I-17
Considerations—INSERT
If you insert a value into a numeric column that is not large enough, an overflow
error occurs.
If a value has more digits to the right of the decimal point than specified by the
scale for the column definition, the value is truncated.
°
INTERVAL values
An INTERVAL data type is compatible only with another INTERVAL data type
with the same range of INTERVAL fields.
°
Date and time values
A date-time data type is compatible only with another date-time data type with
the same range of DATETIME fields.
When you use a range of fields to specify only some of the DATETIME fields
for a DATETIME column, SQL uses the current date and time for any missing
fields to the left of the fields for which values are specified. For missing fields to
the right of the fields for which values are specified, SQL uses the following
values:
YEAR -- Current year HOUR -- 00
MONTH -- 01 MINUTE -- 00
DAY -- 01 SECOND -- 00
FRACTION -- 000000
°
SYSKEY values
For a table with relative organization, the value of a SYSKEY cannot exceed
4294963199 and cannot be greater than the maximum number of rows the table
can contain. For a table with key-sequenced organization, the value of a
SYSKEY cannot exceed 2**63 minus 1.
If you insert rows into a protection view defined with a WHERE clause that
refers to the SYSKEY column, you cannot specify APPEND or ANYWHERE
and you must include SYSKEY in the column list. If such a protection view is
also based on a key-sequenced or entry-sequenced table, you cannot insert a row
into the view.
In addition, values in each row inserted must satisfy any constraints on the table or
on the underlying table of the view. (A table constraint is satisfied if the check
condition is not false - that is, it is either true or has an unknown value.) If the view
is defined with WITH CHECK OPTION, the row must satisfy the view selection
criteria specified in the WHERE clause of the AS select-stmt clause in the
CREATE VIEW statement.
If a row does not qualify, SQL stops inserting rows and returns an error message.
•
Inserting null values
To insert a null value, use the keyword NULL. From a program, you can also use an
indicator variable to insert a null value, as described in the NonStop SQL/MP
programming manual for your host language.