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
Summary of Contents for Application Server 10.1.3.4
Page 3: ......
Page 4: ......
Page 10: ...x ...
Page 104: ...Resolving Specific Errors 7 18 Oracle Application Server Adapters for IMS DB User s Guide ...
Page 170: ...Index iv ...
