Open SQL Type Propagation 

When evaluating arithmetic expressions in SQL statements, Open SQL for Java follows the type propagation rules shown in the following table.

Type propagation in arithmetic expressions

	SMALLINT	INTEGER	BIGINT	REAL	DOUBLE	DECIMAL
SMALLINT	SMALLINT	INTEGER	BIGINT	REAL	DOUBLE	DECIMAL
INTEGER	INTEGER	INTEGER	BIGINT	REAL	DOUBLE	DECIMAL
BIGINT	BIGINT	BIGINT	BIGINT	REAL	DOUBLE	DECIMAL
REAL	REAL	REAL	REAL	REAL	DOUBLE	REAL
DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE
DECIMAL	DECIMAL	DECIMAL	DECIMAL	REAL	DOUBLE	DECIMAL

 

The following table defines the result type of the evaluation of aggregate functions in set function expressions. The result of the aggregate functions MIN, MAX and SUM has the same type as its argument. SUM and AVG are only applicable on numeric data types. AVG returns a DECIMAL for exact numeric data types and DOUBLE for approximate numeric data types. The result of COUNT is always INTEGER.

Type propagations in set function specifications

	MIN	MAX	SUM	AVG	COUNT
SMALLINT	SMALLINT	SMALLINT	SMALLINT	DECIMAL	INTEGER
INTEGER	INTEGER	INTEGER	INTEGER	DECIMAL	INTEGER
BIGINT	BIGINT	BIGINT	BIGINT	DECIMAL	INTEGER
REAL	REAL	REAL	REAL	DOUBLE	INTEGER
DOUBLE	DOUBLE	DOUBLE	DOUBLE	DOUBLE	INTEGER
DECIMAL	DECIMAL	DECIMAL	DECIMAL	DECIMAL	INTEGER
VARCHAR	VARCHAR	VARCHAR			INTEGER