|
Oracle® Application Server Adapter for IMS/DB User's Guide
10g Release 2 (10.1.2) B15806-01 |
|
 Previous |
 Next |
|
Oracle® Application Server Adapter for IMS/DB User's Guide
10g Release 2 (10.1.2) B15806-01 |
|
|
Previous |
Next |
OracleAS Adapter for IMS/DB provides basic support for standard ANSI '92 SQL along with a number of enhancements, all of which can be used when specifying adapter interactions.
This appendix contains the following sections:
OracleAS Adapter for IMS/DB support SELECT, DELETE, INSERT, and UPDATE statements, when specified in interactions.
OracleAS Adapter for IMS/DB enables you to use the following operators in SQL statements:
Arithmetic Operators: Includes the +, -, /, and * operators:
Comparison Operators: Includes the following operators:
=
>
<
>=
<=
<>, !=, ^=
IS NOT NULL
IS NULL
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 C-3 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 one of the columns 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.
