Convert numbers and strings

Before can convert a value from one data type to another, it must determine whether the conversion is meaningful.

The following table shows possible conversions between numeric data types and character data types. In this figure, N represents a value with a numeric data type (such as DECIMAL, FLOAT, or SMALLINT) and C represents a value with a character data type (such as CHAR or VARCHAR).

If conversion is not possible, either because it makes no sense or because the target variable is too small to accept the converted value, returns values that the Results column in the following table describes.

Table 1. Data conversion problems and results
Conversion	Problem	Results
C C	Does not fit	truncates the string, sets a warning (sqlca.sqlwarn.sqlwarn1 to `W` and SQLSTATE to `01004`), and sets any indicator variable to the size of the original string.
N C	None	creates a string for the numeric value; it uses an exponential format for large or small numbers.
N C	Does not fit	fills the string with asterisks, sets a warning (sqlca.sqlwarn.sqlwarn1 to `W` and SQLSTATE to `01004`), and sets any indicator variable to a positive integer. When the fractional part of a number does not fit in a character variable, rounds the number. Asterisks are displayed only when the integer part does not fit.
C N	None	determines the numeric data type based on the format of the character value; if the character contains a decimal point, converts the value to a DECIMAL value.
C N	Not a number	The number is undefined; sets sqlca.sqlcode and SQLSTATE to indicate a runtime error.
C N	Overflow	The number is undefined; sets sqlca.sqlcode and SQLSTATE to indicate a runtime error.
N N	Does not fit	attempts to convert the number to the new data type. For information about possible errors, see the HCL OneDB™ Guide to SQL: Reference.
N N	Overflow	The number is undefined; sets sqlca.sqlcode and SQLSTATE to indicate a runtime error.

In Data conversion problems and results, the phrase Does not fit means that the size of the data from the source variable or column exceeds the size of the target column or variable.