Skip Headers
Oracle® Application Server Adapter for IMS/DB User's Guide
10g Release 2 (10.1.2)
B15806-01
  Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

C Supported SQL Syntax and SQL Enhancements

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:

Supported SQL Statements

OracleAS Adapter for IMS/DB support SELECT, DELETE, INSERT, and UPDATE statements, when specified in interactions.

Supported Operators

OracleAS Adapter for IMS/DB enables you to use the following operators in SQL statements:

Supported Functions

OracleAS Adapter for IMS/DB enables the use of the following functions in SQL statements:

Table C-1 String Functions

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

Table C-2 Group Functions

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

SQL Enhancements

You can incorporate the following SQL enhancements into the adapter interactions to handle hierarchical data in IMS/DB.

Generating Hierarchical Results

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.

Accessing Hierarchical Data Using SQL

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]

Flattening Hierarchical Data Using SQL

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.

Using an Alias

To list the hierarchical data with the parent data only, you must use an alias for the child data.


Note:

Without an alias the query lists, for each parent row, all child rows of all parent rows.