OracleAS Adapter for VSAM 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 VSAM supports `SELECT`

, `DELETE`

, `INSERT`

, and `UPDATE`

statements, when specified in interactions.

OracleAS Adapter for VSAM enables the use of the following operators in SQL statements:

OracleAS Adapter for VSAM 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 VSAM.

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 VSAM 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.