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 SAP Sybase SQL Anywhere®® and Adaptive Server 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, 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-compatible synonym for NUMERIC(19,4), allowing NULL.
- SMALLMONEY is an Adaptive Server-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.