Table of Contents Previous Next


2 The SQL Language : 2.4 Functions and Operators : 2.4.3 Mathematical Functions and Operators

Mathematical operators are provided for many Advanced Server types. For types without common mathematical conventions for all possible permutations (e.g., date/time types) the actual behavior is described in subsequent sections.
Note: If the db_dialect configuration parameter in the postgresql.conf file is set to redwood, then division of a pair of INTEGER data types does not result in a truncated value. Any fractional result is retained as shown by the following example:
This behavior is compatible with Oracle databases where there is no native INTEGER data type, and any INTEGER data type specification is internally converted to NUMBER(38), which results in retaining any fractional result.
If the db_dialect configuration parameter is set to postgres, then division of a pair of INTEGER data types results in a truncated value as shown by the following example:
This behavior is compatible with PostgreSQL databases where division involving any pair of INTEGER, SMALLINT, or BIGINT data types results in truncation of the result. The same truncated result is returned by Advanced Server when db_dialect is set to postgres as shown in the previous example.
Note however, that even when db_dialect is set to redwood, only division with a pair of INTEGER data types results in no truncation of the result. Division that includes only SMALLINT or BIGINT data types, with or without an INTEGER data type, does result in truncation in the PostgreSQL fashion without retaining the fractional portion as shown by the following where INTEGER and SMALLINT are involved in the division:
LOG(b NUMBER, x NUMBER)
MOD(y, x)
NVL(x, y)
If x is null, then NVL returns y
POWER(a DOUBLE PRECISION, b DOUBLE PRECISION)
a raised to the power of b
POWER(a NUMBER, b NUMBER)
a raised to the power of b
ROUND(v NUMBER, s INTEGER)
Round to s decimal places
TRUNC(v NUMBER, s INTEGER)
WIDTH_BUCKET(op NUMBER, b1 NUMBER, b2 NUMBER, count INTEGER)
Return the bucket to which op would be assigned in an equidepth histogram with count buckets, in the range b1 to b2
ATAN2(x, y)

2 The SQL Language : 2.4 Functions and Operators : 2.4.3 Mathematical Functions and Operators

Table of Contents Previous Next