|
Oracle® Application Server Adapter for VSAM User's Guide
10g Release 2 (10.1.2) B15804-01 |
|
 Previous |
 Next |
|
Oracle® Application Server Adapter for VSAM User's Guide
10g Release 2 (10.1.2) B15804-01 |
|
|
Previous |
Next |
OracleAS Adapter for VSAM 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:
The OracleAS Adapter for VSAM provides full support for SELECT, DELETE, INSERT, and UPDATE statements, when specified in interactions.
The OracleAS Adapter for VSAM enables the use of the following operators in SQL statements:
The 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 C-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 one of the columns 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.
