When you perform arithmetic on values with different datatypes, Adaptive Server must determine the datatype and, in some cases, the length and precision, of the result.
Each system datatype has a datatype hierarchy, which is stored in the systypes system table. User-defined datatypes inherit the hierarchy of the system type on which they are based.
The following query ranks the datatypes in a database by hierarchy. In addition to the information shown below, query results include information about any user-defined datatypes in the database:
select name, hierarchy from systypes order by hierarchy
name hierarchy ---------- --------- floatn 1 float 2 datetimn 3 datetime 4 real 5 numericn 6 numeric 7 decimaln 8 decimal 9 moneyn 10 money 11 smallmoney 12 smalldatet 13 intn 14 uintn 15 bigint 16 ubigint 17 int 18 uint 19 smallint 20 usmallint 21 tinyint 22 bit 23 univarchar 24 unichar 25 unitext 26 sysname 27 varchar 27 nvarchar 27 longsysnam 27 char 28 nchar 28 timestamp 29 varbinary 29 binary 30 text 31 image 32 date 33 time 34 daten 35 timen 36 bigdatetim 37 bigtime 38 bigdatetim 39 bigtimen 40 extended t 99
uinteger_type (for
example, usmallint) is an internal representation. The
correct syntax for unsigned types is unsigned {int | integer | bigint | smallint }.
The datatype hierarchy determines the results of computations using values of different datatypes. The result value is assigned the datatype that is closest to the top of the list.
In the following example, qty from the sales table is multiplied by royalty from the roysched table. qty is a smallint, which has a hierarchy of 20; royalty is an int, which has a hierarchy of 18. Therefore, the datatype of the result is an int.
smallint(qty) * int(royalty) = int
This example multiplies an int, which has a hierarchy of 18; with an unsigned int, which has a hierarchy of 19, and the datatype of the result is a int:
int(10) * unsigned int(5) = int(50)
Unsigned integers are always promoted to a signed datatype
when you use a mixed-mode expression. If the unsigned integer value
is not in the signed integer range, Adaptive Server issues a conversion
error.
See the Reference Manual: Building Blocks for more information about the datatype hierarchy.
