Mixed-Mode Arithmetic and Datatype Hierarchy

When you perform arithmetic on values with different datatypes, SAP ASE 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 SAP ASE datatype hierarchy applies only to computations or expressions involving numeric datatypes.

When comparing different datatypes related to date or time (for example, datetime versus date), only components that are present in both datatypes are compared. For example, SAP ASE considers the datetime value “20-Nov-2012 23:24:25” equal to the date value “20-Nov-2012” since it compares only the date component (in this case, the string “20-Nov-2012”). This is complaint with the ANSI SQL standard.

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

Note: 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)

Note: 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, SAP ASE issues a conversion error.

See the Reference Manual: Building Blocks for more information about the datatype hierarchy.