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.