User's Manual

SQL Enhancements
Supported SQL Syntax and SQL Enhancements D-3
SQL Enhancements
You can incorporate the following SQL enhancements into the adapter interactions to
handle hierarchical data in IMS/DB.
■ Generating Hierarchical Results
■ Accessing Hierarchical Data Using SQL
■ Flattening Hierarchical Data Using SQL
Generating Hierarchical Results
A hierarchical query nests a SELECT statement as one of the columns of the rowset
retrieved by a nested SELECT statement.
Use braces ({}) to delimit the nesting.
Max Max Max(exp) Returns the maximum value of the expression exp
Min Min Min(exp) Returns the minimum value of the expression exp
Sum Sum Sum(exp) Returns the summation of the expression exp
Table D–5 Mathematical Functions
Oracle
Function
Oracle Connect
Function Usage Comment
Abs Abs Abs(n) Returns the absolute value of n
Ceil Ceil Ceil(n)Returns n rounded up to the closest integer
Cos Cos Cos(n) Returns the cosine value of n
Exp Exp Exp(n) Returns the exponential value of n
Floor Floor Floor(n)Returns n rounded down to the closest integer
Ln Ln Ln(n) Returns the natural log value of n
Log Log Log(n) Returns the log value of n
Mod Mod Mod(n, m) Returns the integer value after dividing n by m
Nvl Nvl Nvl(exp1, exp2)Returns exp2 when exp1 is null
Power Power Power(n, m)Returns n to the power of m
Round Round Round(n, m)Returns n with the fractional part rounded to m digits
Sin Sin Sin(n) Returns the sine value of n
Sqrt Sqrt Sqrt(n) Returns the square root of n
Tan Tan Tan(n) Returns the tangent value of n
Trunc Trunc Trunc(n, m) Returns the absolute value of n
Table D–4 (Cont.) Group Functions
Oracle
Function
Oracle Connect
Function Usage Comment