Determine the Datatype Hierarchy

Each system datatype has a datatype hierarchy, which is stored in the systypes system table. User-defined datatypes inherit the hierarchy of the system datatype on which they are based.

The datatype hierarchy applies only to computations or expressions involving numeric datatypes. SAP ASE converts all terms involved first to the datatype highest in the hierarchy before the expression is evaluated or the comparison is performed. For example, when adding and int to a float, the resulting sum has a float datatype.

That is, the SAP ASE server 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 compliant with the ANSI SQL standard.

The following query ranks the datatypes in a database by hierarchy. In addition to the information shown below, your 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 
bigdatetime       37 
bigtime           38 
bigdatetimen      39 
bigtimen          40 
xml               41
extended time     99
Note: u<int_type> 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 or has the least hierarchical value.

In this 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