Format
{ arithmetic-expr }
{ builtin-expr }
{ case-expr }
{ }
{ char-string-literal }
{ concatenated-expr }
{ external-literal }
{ }
{ field-or-record-expr }
{ first-from-expr }
{ numeric-literal }
{ }
{ statistical-expr }
1 – Parameters
1.1 – arithmetic-expr
{ + }
{ - }
value-expr { * } value-expr
{ / }
An arithmetic expression combines value expressions and
arithmetic operators. When you use an arithmetic expression in a
value expression, the product using the CDO expression calculates
the value associated with the expression and uses that value when
executing the statement. Therefore, an arithmetic expression must
be reducible to a value.
The value expression, value-expr, is a symbol or a string of
symbols used to calculate a value.
1.2 – builtin-expr
{ NULL }
{ [ ] }
{ [ [ BOTH ] ] }
{ TRIM ( [ [ LEADING ] CHARACATER value-expr FROM ] value-expr ) }
{ [ [ TRAILING ] ] }
{ [ ] }
{ POSITION (value-expr IN value-expr FROM value-expr ) }
{ USER }
{ CURRENT_USER }
{ CURRENT_DATE }
{ CURRENT_TIME (scale) }
{ CURRENT_TIMESTAMP (scale) }
{ CHARACTER_LENGTH (value-expr) }
{ CHAR_LENGTH (value-expr) }
{ OCTET_LENGTH (value-expr) }
{ UPPER (value-expr) }
{ LOWER (value-expr) }
{ SESSION_USER }
{ SUBSTRING [_OCTETS] (value-expr FROM value-expr FOR value-expr) }
{ SUBSTRING_CHARACTERS (value-expr FROM value-expr FOR value-expr) }
{ SYSTEM_USER }
{ cast-builtin-expr }
{ { YEAR } }
{ { MONTH } }
{ { DAY } }
{ { HOUR } }
{ EXTRACT ( { MINUTE } FROM value-expr) }
{ { SECOND } }
{ { WEEKDAY } }
{ { JULIAN } }
{ TRANSLATE (value-expr USING character-set) }
Calculate values based on specified value expressions. See the
Descriptions subtopic for Built-in Function descriptions.
See the Oracle Rdb7 SQL Reference Manual for the character set
types.
1.3 – cast-builtin-expr
CAST (value-expr AS
{ }
{FIELD field-name }}
{DATATYPE }}
{{ }}
{{TEXT [CHARACTER_SET IS name] [SIZE IS digits] [CHARACTERS] }}
{{ [OCTETS ] }}
{{ }}
{{VARYING STRING [CHARACTER_SET IS name [SIZE IS digits] [CHARACTERS]}}
{{ [OCTETS ]}}
{{ }}
{{DATE [VMS ] }}
{{ [ANSI] }}
{{ }} )
{{TIME [SCALE scale-value] }}
{{TIMESTAMP [SCALE scale-value] }}
{{interval-builtin-expr }}
{{F_FLOATING }}
{{G_FLOATING }}
{{ }}
{{ {BYTE } }}
{{ {WORD } }}
{{[SIGNED] {LONGWORD} [SCALE scale-value] }}
{{ {QUADWORD} }}
{ }
interval-builtin-expr
{YEAR [SIZE IS digits] [TO MONTH] }
{MONTH [SIZE IS digits] }
{ }
{ [ ]}
{ [ { HOUR }]}
{DAY [SIZE IS digits] [ TO { MINUTE }]}
{ [ { SECOND [SCALE scale-value] }]}
{ [ ]}
{ }
INTERVAL{ [ ] }
{ [ {MINUTE }] }
{HOUR [SIZE IS digits] [ TO {SECOND [SCALE scale-value] }] }
{ [ ] }
{ }
{MINUTE [SIZE IS digits] [TO SECOND [SCALE scale-value] ] }
{SECOND [SIZE IS digits] [SCALE scale-value] }
{ }
Converts a value expression to another data type.
RESTRICTION
The CAST builtin expression requires a space between the
letter T in CAST and the open parenthesis character of the
value expression; otherwise, a syntax error occurs.
1.4 – case-expr
CASE value-expr { WHEN value-expr THEN value-expr } ...
[ ELSE value-expr ] END
Matches two value expressions for equality. This clause is
identical to the SQL SIMPLE CASE expression.
1.5 – char-string-literal
Specifies a string of printable characters. See
character_string_literals help topic for more information.
1.6 – concatenated-expr
{ | }
value-expr { ^ } value-expr ...
Combines two value expressions by joining the second expression
to the end of the first expression.
You can combine value expressions of any kind, including numeric
expressions, string expressions, and literals.
The vertical bar (|) specifies a value through combining one or
more value expressions. The circumflex character (^) specifies a
value through combining one or more value expressions using SQL
concatenation rules.
1.7 – external-literal
EXTERNAL quoted-string
Specifies the name of an external literal. Defines the equivalent
of the COBOL initial value (VALUE IS EXTERNAL clause) and level
88 condition values (VALUES ARE EXTERNAL clause).
1.8 – field-or-record-expr
{ }
{ dir-name }
{ { name IN } ... context-var }
{ }
Specifies the name of a field or a record in a database by
directory name, or by field or record name and context variable.
A context variable is a temporary name you associate with a
record. You define a context variable in a record selection
expression (RSE). You specify a context variable only when you
use the name IN parameter of the field or record expression
syntax.
For example, once you define E as the context variable for the
EMPLOYEES relation, LAST_NAME IN E is a value expression that
refers to a value from the LAST_NAME field of EMPLOYEES. Use name
IN only in an expression with a context variable.
1.9 – first-from-expr
FIRST value-expr FROM rse
Specifies a value by forming a record stream (as indicated by a
record selection expression). If at least one record matches the
RSE, the values stored in the first record of the record stream
are used to evaluate the value expression.
The FIRST FROM expression can perform the equivalent of a table
lookup when you are sure that the value you want to find in a
relation is unique.
The value expression, value-expr, is a symbol or a string of
symbols used to calculate a value. The rse parameter specifies a
clause that products use at run time to include specific records
for processing.
1.10 – numeric-literal
Specifies a value that can be expressed as a decimal number. See
numeric_literals help topic for more information.
1.11 – statistical-expr
{ }
{ { MAX } }
{ { MIN } }
{ { TOTAL } value-expr } OF rse
{ { AVERAGE } }
{ }
{ COUNT }
{ }
Specifies a value by forming a record stream (as indicated
by a record selection expression), and evaluating its value
expression against every record in the record stream. Statistical
expressions are sometimes called aggregate expressions because
they calculate a single value for a collection of records. When
you use a statistical expression (except for COUNT), you specify
a value expression and an RSE. A layered product evaluates the
value expression for each record in the record stream formed by
the RSE. Then the product calculates a single value based on the
results of the first step.
The COUNT expression differs from the other statistical operators
because it operates on the record stream defined by the RSE,
rather than on values in that record stream. It returns the
number of records in the record stream. In the following
expression, the number of employees working in New Hampshire
is returned.
COUNT OF E IN EMPLOYEES WITH STATE IN E = "NH"
2 – Description
A value expression returns a value that can be a string, a
number, or a null value.
Arithmetic Operators describes the operators used in arithmetic
expressions.
Table 5-2 Arithmetic Operators
Symbol Function
+ Addition
- Subtraction
* Multiplication
/ Division
Statistical Operators describes the operators used in statistical
expressions.
Table 5-3 Statistical Operators
Function Value of Function
AVERAGE The average of the values specified by the value
expression for all records specified by the RSE. The
value expression must be a numeric data type.
COUNT The number of records in the stream specified by the
RSE.
MAX The largest of the values specified by the value
expression for all records specified by the RSE.
MIN The smallest of the values specified by the value
expression for all records specified by the RSE.
TOTAL The sum of the values specified by the value expression
for all records specified by the RSE. The value
expression must be a numeric data type.
Built-in Function Description describes the built-in function
names and values. See the Oracle Rdb7 SQL Reference Manual for
more details on the use and restrictions for using SQL built-in
functions.
Table 5-4 Built-in Function Description
Name Description
NULL Specifies a null value.
TRIM Removes leading or trailing characters from any
character value expression. Note: The CHARACTER
keyword is required in CDO.
POSITION Searches for a string in a character value
expression.
USER Specifies the user name of the current process.
CURRENT_USER Returns the current active user name for a
request.
CURRENT_DATE Returns a DATE data type value containing year,
month, and day for today's date.
CURRENT_TIME Returns a TIME data type value containing hours,
minutes, and seconds for the current time. You
can specify a fractional precision between 0 and
2 for the seconds returned by CURRENT_TIME. The
fractional seconds precision is a number that
designates the number of digits returned in the
field. The fractional precision is the negative
of the value specified in the SCALE clause. The
CURRENT_TIME keyword and the left parenthesis for
the fractional precision must be separated by a
space. Otherwise, CDO interprets it as the name of
an element with a version of the value specified
in the fractional precision.
CURRENT_ Returns a TIMESTAMP data type value containing
TIMESTAMP year, month, and day for today's date and hours,
minutes, and seconds for the current time. You
can specify a fractional precision between 0 and
2 for the seconds returned by CURRENT_TIMESTAMP.
The fractional seconds precision is a number that
designates the number of digits returned in the
field. The fractional precision is the negative
of the value specified in the SCALE clause. The
CURRENT_TIMESTAMP keyword and the left parenthesis
for the fractional precision must be separated
by a space. Otherwise, CDO interprets it as the
name of an element with a version of the value
specified in the fractional precision.
CHARACTER_ Calculates the length of a value expression of
LENGTH any data type. You can use CHAR_LENGTH as an
alternative for CHARACTER_LENGTH.
OCTET_LENGTH Calculates the length, in octets, of a value
expression of any data type.
UPPER Converts all lowercase characters in a value
expression to uppercase characters.
LOWER Converts all uppercase characters in a value
expression to lowercase characters.
SESSION_USER Returns the current active session user name.
SUBSTRING Returns portions of character value expressions.
SYSTEM_USER Returns the user name of the login process at the
time of the database attachment.
CAST Converts a value expression to another data type.
EXTRACT Returns a single date-time field expressed as an
integer from a field defined with a data type of
DATE, TIME, TIMESTAMP, or INTERVAL.
TRANSLATE Translates a character value expression from one
character set to another compatible character set,
such as RDB$KANJI to Kanji.
3 – Examples
1.(8 + 14) / 2 - 4
In this example, the arithmetic expression evaluates as 7.
2.DEFINE FIELD NAME
COMPUTED BY FIRST_NAME | ' ' | MIDDLE_INITIAL | ' ' | LAST_NAME.
The output is:
JOHN Q PUBLIC
In this example, the concatenated expression combines three
fields into the NAME field definition. The space between each
pair of quotation marks appears in the output of the NAME
field.
3.COUNT OF E IN EMPLOYEES WITH
LAST_NAME IN FULL_NAME IN E = "SMITH".
In this example, the FIELD or RECORD expression specifies
the LAST_NAME field in the FULL_NAME record in the EMPLOYEES
relation.
4.FIRST SALARY IN E FROM E IN EMPLOYEES
WITH LOCATION IN E = "BUILDING_A"
In this example, the FIRST FROM expression finds the salary of
the first employee who works in BUILDING_A.
5.AVERAGE SALARY_AMOUNT IN CS OF CS IN SALARY WITH SALARY_AMOUNT IN CS GT
50000
In this example, the AVERAGE statistical expression uses the
RSE to form a stream of records where the SALARY_AMOUNT field
is greater than 50,000. The average of the values is calculated
by the product reading the expression.
6.MAX SALARY_AMOUNT IN CS OF SAL IN CURRENT_SALARY WITH SALARY IN SAL = MAX
This example shows how to use the MAX expression to find the
highest paid employee in the company.
7.MIN SALARY_AMOUNT IN CS OF SAL IN CURRENT_SALARY WITH SALARY IN SAL = MIN
In this example, the MIN expression finds the lowest paid
employee in the company.
8.TOTAL SALARY_AMOUNT IN CS OF CS IN CURRENT_SALARY
The TOTAL expression finds the total annual payroll of the
company.
9.8 + 7
This example shows an arithmetic expression that adds two
numeric literals.
108 + 14 / 2 - 4
This is an example of an arithmetic expression that is
evaluated as 11.
118 + 14 / (2 - 4)
In this example, the arithmetic expression is evaluated as 1.