C
Supported SQL Syntax and SQL Enhancements

The Oracle Application Server Integration 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

• Supported Operators

• Supported Functions

• SQL Enhancements

Supported SQL Statements

The Oracle Application Server Integration Adapter for IMS/DB provides full support for SELECT, DELETE, INSERT, and UPDATE statements, when specified in interactions.

Supported Operators

The Oracle Application Server Integration Adapter for IMS/DB enables the use of the following operators in SQL statements:

•  Arithmetic Operators

•  Comparison Operators

  Table C-1  Arithmetic Operators

  Operator
  +
  -
  *
  /

  Table C-2  Comparison Operators

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

Supported Functions

The Oracle Application Server Integration Adapter for IMS/DB enables the use of the following functions in SQL statements:

•  String Functions

•  Group Functions

•  Mathematical Functions

  Table C-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 C-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 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

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