NUMERIC data types

Sybase ASE offers numeric data types which are quite similar to Informix^® numeric data types. This table shows general conversion rules for numeric data types:

Table 1. Numeric data types (Informix vs. Sybase ASE)
Informix	Sybase ASE
SMALLINT	SMALLINT
INTEGER (synonym: INT)	INTEGER (synonym: INT)
BIGINT	BIGINT
INT8	BIGINT
DECIMAL[(p[,s)] (synonyms: DEC, NUMERIC) DECIMAL(p,s) defines a fixed point decimal where p is the total number of significant digits and s the number of digits that fall on the right of the decimal point. DECIMAL(p) defines a floating point decimal where p is the total number of significant digits. The precision p can be from 1 to 32. DECIMAL is treated as DECIMAL(16).	DECIMAL[(p[,s)] (synonyms: DEC, NUMERIC) DECIMAL[(p[,s])] defines a fixed point decimal where p is the total number of significant digits and s the number of digits that fall on the right of the decimal point. The precision p can be from 1 to 38. The default precision is 18 and the default scale is 0: DECIMAL in Sybase ASE = DECIMAL(18,0) in Informix DECIMAL(p) in Sybase ASE = DECIMAL(p,0) in Informix
MONEY[(p[,s])	Sybase ASE provides the MONEY and SMALLMONEY data types, but the currency symbol handling is quite different. Therefore, Informix MONEY columns should be implemented as DECIMAL columns in Sybase ASE.
SMALLFLOAT (synonyms: REAL)	REAL
FLOAT[(n)] (synonyms: DOUBLE PRECISION) The precision (n) is ignored.	DOUBLE PRECISION

Sybase ASE does not support implicit character string to numeric conversions. For example, if you compare an integer column to '123' in a WHERE clause, Sybase will raise a conversion error. The problem exists also when using CHAR or VARCHAR SQL parameters.

Solution

In BDL programs

When creating tables from BDL programs, the database interface automatically converts Informix data types to corresponding Sybase ASE data types.

There is no Sybase ASE equivalent for the Informix DECIMAL(p) floating point decimal (i.e. without a scale). If your application is using such data types, you must review the database schema in order to use Sybase ASE compatible types. To workaround the Sybase ASE limitation, the Sybase ASE database drivers convert DECIMAL(p) types to a DECIMAL( 2*p, p ), to store all possible numbers an Informix DECIMAL(p) can store. However, the original Informix precision cannot exceed 19, since Sybase ASE maximum DECIMAL precision is 38(2*19). If the original precision is bigger as 19, a CREATE TABLE statement executed from a Genero program will fail with an Sybase ASE error 2756.

Database creation scripts

SMALLINT and INTEGER columns do not have to use another data type in Sybase ASE.
For DECIMALs, check the precision limit. Always use a precision and a scale.
Convert MONEY columns to DECIMAL(p,s) columns. Always use a precision and a scale.
Convert SMALLFLOAT columns to REAL columns.
Since FLOAT precision is ignored in Informix, convert this data type to FLOAT(15).

Since Sybase ASE does not support implicit character string to numeric conversions, you must check that your programs do not use string literals or CHAR/VARCHAR SQL parameters in integer expressions, as in this example:

DEFINE pv CHAR(1)
CREATE TABLE mytable ( v1 INT, v2 INT )
LET pv = '1'
SELECT * FROM mytable WHERE v1 = '1' AND v2 = pv