HPlogo ALLBASE/SQL Reference Manual > Chapter 7 Data Types

Type Conversion

MPE documents

Complete PDF
Table of Contents
Index

E0300 Edition 9 ♥
E0399 Edition 8
E0897 Edition 7

ALLBASE/SQL converts the type of a value in the following situations:

  • Including values of different types in the same expression.

  • Moving data from a host variable to a column or a column to a host variable of a different type.

The valid type combinations are shown in Table 7-3 "Valid Type Combinations"

Table 7-3 Valid Type Combinations

Source Data Type Target Data Type
CHAR or VARCHARCHAR or VARCHAR
DATE, TIME DATETIME, or INTERVAL when CHAR value involved in date/time math or inserted into or compared to a date/time column
CHAR or VARCHARBINARY or VARBINARY (from host variable/constant into a binary column only)
BINARY or VARBINARYBINARY or VARBINARY
BINARY or VARBINARYCHAR or VARCHAR (from column into host variable, or comparing a binary column with a char column or value)
DECIMAL, FLOAT, REAL, INTEGER, SMALLINTAny numeric type
DATE, TIME, DATETIME, INTERVALCHAR or VARCHAR (except in LIKE predicate)

In some cases, such as the following, data conversion can lead to overflow or truncation:

  • Overflow can occur during these conversions:

    FLOAT

    to DECIMAL, INTEGER or SMALLINT

    FLOAT

    to REAL

    REAL

    to DECIMAL, INTEGER, or SMALLINT

    DECIMAL

    to DECIMAL, INTEGER, or SMALLINT

    INTEGER

    to DECIMAL or SMALLINT

    SMALLINT

    to DECIMAL

  • Overflow of the integer part and truncation of the fractional part of a number can occur during a FLOAT-to-DECIMAL conversion, because ALLBASE/SQL aligns the decimal points.

  • Truncation of the fractional part of a value occurs during these conversions:

    DECIMAL

    to SMALLINT or INTEGER

    DECIMAL

    to DECIMAL when the target scale is smaller than the source scale

    FLOAT

    to INTEGER, SMALLINT, DECIMAL, or REAL

    REAL

    to INTEGER, SMALLINT, or DECIMAL

  • Truncation can occur during these conversions if the target is too small:

    DATE, TIME, DATETIME or INTERVAL

    to VARCHAR or CHAR

    CHAR

    to VARCHAR, BINARY or VARBINARY

    VARCHAR

    to CHAR, BINARY or VARBINARY

    VARBINARY

    to BINARY, CHAR or VARCHAR

    BINARY

    to VARBINARY, CHAR, or VARCHAR

When you use numeric data of different types in an expression or comparison operation, the data type of the lesser type is converted to that of the greater type, and the result is expressed in the greater type. Numeric types have the following precedence:

  • FLOAT

  • REAL, DECIMAL

  • INTEGER

  • SMALLINT

Comparison operations or expressions involving different numeric data types result in conversion from one data type to another as explained in Table 7-4 "Conversions from Combining Different Numeric Data Types".

Table 7-4 Conversions from Combining Different Numeric Data Types

Operations containing: Result:
DECIMAL and INTEGER types only All participating integers are converted to DECIMAL quantities having a precision of 10 and a scale of 0.
DECIMAL and SMALLINT types only All participating SMALLINT values are converted to DECIMAL quantities having a precision of 5 and a scale of 0.
One item of type FLOAT All participating integer and decimal operands are converted to FLOAT quantities and precision can be lost.
One item of type REAL All arithmetic involving REAL operands results in a type of FLOAT. All participating integer and decimal operands are converted to FLOAT quantities and precision can be lost.



Underflow

Null Values