D Supported SQL Syntax and SQL Enhancements

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:

Supported SQL Statements
Supported Operators
Supported Functions
SQL Enhancements

Supported SQL Statements

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

Supported Operators

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

Arithmetic Operators
Comparison Operators

Table D-1 Arithmetic Operators

Operator
+
-
*
/

Table D-2 Comparison Operators

Operator
=
>
<
>=
<=
<>, !=, ^=
IS NOT NULL
IS NULL

Supported Functions

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

String Functions
Group Functions
Mathematical Functions

Table D-3 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 D-4 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 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

SQL Enhancements

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

Generating Hierarchical Results
Accessing Hierarchical Data Using SQL
Flattening Hierarchical Data Using SQL

Generating Hierarchical Results

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.

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 of the children of all of the parent rows.