OracleAS Adapter for IMS/DB provides basic support for standard ANSI '92 SQL along with several enhancements, all of which can be used when specifying adapter interactions.
This appendix contains the following sections:
OracleAS Adapter for IMS/DB supports SELECT, DELETE, INSERT, and UPDATE statements, when specified in interactions.
OracleAS Adapter for IMS/DB enables the use of the following operators in SQL statements:
OracleAS Adapter for IMS/DB enables the use of the following functions in SQL statements:
| Oracle Function | Oracle Connect Function | Usage | Comment |
|---|---|---|---|
|
|| |
|| |
str1 || str2 |
Returns a string consisting of str1 concatenated with str2 |
|
Ascii |
Ascii |
Ascii(c) |
Returns the ASCII value of c |
|
Chr |
Chr |
Chr(ascii) |
Returns the character corresponding to the given ASCII value |
|
Instr |
Position |
Position(str2, str1) |
Returns an index in str2 to the first occurrence of str1 in str2 |
|
Length |
Length |
Length(str) |
Returns the number of bytes of str |
|
Lower |
Lower |
Lower(str) |
Returns str in lowercase |
|
Lpad |
Lpad |
Lpad(str, n) |
Returns str with n leading blanks |
|
Ltrim |
Ltrim |
Ltrim(str) |
Returns str with leading blanks removed |
|
Rpad |
Rpad |
Rpad(str, n) |
Returns str with n trailing blanks |
|
Rtrim |
Rtrim |
Rtrim(str) |
Returns str with trailing blanks removed |
|
Substr |
Substr |
Substr(str, n [, m]) |
Returns a substring of str, starting with the nth character and m characters in length, or until the end of the string if m is not supplied |
|
Upper |
Upper |
Upper(str) |
Returns str in uppercase |
| Oracle Function | Oracle Connect Function | Usage | Comment |
|---|---|---|---|
|
Avg |
Avg |
Avg(exp) |
Returns the average value of the expression exp |
|
Count |
Count |
Count(exp) |
Returns the count of the expression exp |
|
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 |
You can incorporate the following SQL enhancements into the adapter interactions to handle hierarchical data in IMS/DB.
A hierarchical query nests a SELECT statement as a column of the rowset retrieved by a nested SELECT statement.
Use braces ({}) to delimit the nesting.
Data stored hierarchically in a IMS/DB data source can be referenced by using a hyphen followed by a right arrow (->) to denote the parent child relationship in the source:
FROM … parent_name->child1->child2… [alias]
Or, using an alias for the parent table:
FROM … parent_alias->child1->child2… [alias]
You can produce a flattened view of hierarchical data by embedding a SELECT statement inside the list of columns to be retrieved by another SELECT statement. You use parentheses to delimit the nesting. The nested SELECT statement can reference a child rowset (using the parent->child syntax) only in its FROM clause.