Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 236 / 536

Other factors affecting the display of data
216
•
ZEROS
indicates that binary zeros convert to NULLS.
•
literal
indicates that all occurrences of the specified literal convert to
NULLS. The specified literal must match exactly, including leading
and/or trailing blanks, with the value in the input file, for Adaptive Server
IQ to recognize it as a match. You can list up to 20 literal values.
You may need to use additional conversion options on the same column. For
example, to insert ASCII data into an
INT
column, which is stored in binary
format, and convert blanks in the input data to NULLS when inserted, use the
ASCII
conversion option to convert the input to binary and the
NULL
conversion
option to convert blanks to NULLS.
Here is a Windows NT example:
LOAD TABLE lineitem(
l_orderkey NULLS(ZEROS) ASCII(4),
l_partkey ASCII(3),
l_shipdate date(’MM/DD/YY’),
l_suppkey ascii(5),
FILLER(1))
FROM ’C:\\MILL1\\tt.t’
PREVIEW ON
Other factors affecting the display of data
Whenever Adaptive Server IQ requires an explicit or implicit conversion from
one data type to another during a query or insert, it always truncates the results.
The following describes such situations:
• When you explicitly convert data from a higher scale to a lower scale,
Adaptive Server IQ truncates the values in the results. For example, if you
CAST a column value in a query to a scale 2 when it is stored with a scale
4, values such as 2.4561 become 2.45. See Chapter 8, “SQL Functions” in
the Adaptive Server IQ Reference Manual for more information.
• When Adaptive Server IQ implicitly converts from a higher scale to a
lower scale during an insertion, it truncates the values before inserting the
data into the table. For example, if you insert from one table with a data
type of
NUMERIC(7,3) to another table with a data type of DECIMAL(12,2) ,
values such as 2.456 will become 2.45.