Data Conversion on Insertion

The data you enter into the SAP Sybase IQ database may come from diverse sources.

Not all of your data will match the SAP Sybase IQ data types exactly. Some of it will need to be converted. Data is converted in two ways: explicitly and implicitly. For example, to insert INT data into a CHAR column you must convert it explicitly.

Implicit conversions can occur when you:

Insert data selected from another column in the same database
Insert data selected from another database
Load data from a flat file

When an explicit conversion is needed, the way you specify the conversion depends on whether you are loading from a flat file or inserting selected rows:

In the LOAD TABLE statement, convert data explicitly by specifying a format in the column-spec.
In the INSERT statement, convert data explicitly using the data conversion functions CAST, CONVERT, and DATEPART in the SELECT statement or VALUES list.

While most SAP Sybase IQ data types are fully compatible with SQL Anywhere and Adaptive Server Enterprise data types of the same name, there are some differences.

For compatibility among versions, a few data types have been defined as synonyms of other data types:

DECIMAL is a synonym for NUMERIC.
INTEGER is a synonym for INT.
DATETIME is a synonym for TIMESTAMP.
FLOAT (precision) is a synonym for REAL or DOUBLE, depending on the value of precision. For Adaptive Server Enterprise, REAL is used for precision less than or equal to 15, and DOUBLE for precision greater than 15. For SAP Sybase IQ and SQL Anywhere, the cutoff is platform-dependent, but on all platforms the cutoff value is greater than 22.
MONEY is an Adaptive Server Enterprise-compatible synonym for NUMERIC(19,4), allowing NULL.
SMALLMONEY is an Adaptive Server Enterprise-compatible synonym for NUMERIC(10,4), allowing NULL.

You can use a synonym interchangeably with its standard data type. Data is stored internally as the standard data type, where synonyms exist. In error messages, the standard name appears in place of the synonym.

Note: By default, SAP Sybase IQ assumes that input data is binary (numeric data) and tries to insert it that way. However, this presumes that the input column length in bytes must match the destination column length in bytes. If not, the insert fails or leads to unexpected results. For example, if you attempt to insert an input column with integer data of 4 bytes into a SMALLINT destination column, SAP Sybase IQ loads only the first 2 bytes of that input column.

Data from SAP Sybase IQ 12.7
If you migrate data into SAP Sybase IQ from version 12.7, you must convert data types before inserting or loading them, as outlined in the Migration.
Load Conversion Options
There are several conversion options for loading from flat files using the LOAD TABLE statement.
Column Width Issues
SAP Sybase IQ assumes the width of the input data is the same as the destination column width and reads the input file accordingly.
Faster Date and Time Loads
SAP Sybase IQ has performance optimizations built in for ASCII-to-binary conversions on date, time, and datetime data during loads. If the raw data you are loading exactly matches one of these formats, you can significantly decrease load time by using the appropriate format.
ASCII Input Conversion
Convert ASCII input data to binary.
The DATE Option
Use the DATE conversion option to insert ASCII data that is stored in a fixed format into a DATE column.
The DATETIME Conversion Option
Use the DATETIME conversion option to insert ASCII data that is stored in a fixed format into a TIME, TIMESTAMP, or DATETIME column.
NULL Data Conversions
Use the NULL conversion option to convert specific values in the input data to NULLs when inserting into SAP Sybase IQ column indexes.