CDO provides the following expressions:
o Value expressions-to calculate a value
o Conditional expressions-to represent a relationship between
values
o Record selection expressions (RSE)-to state a condition for
processing
CDO stores expressions in a generic format, not as text, so that
many products and applications can share the same expression.
The product using the CDO expression calculates the value at run
time.
1 – precedence_ordering
The following list shows the order in which Oracle CDD/Repository
interprets symbols used in an expression:
1. (symbols)
Any field contained in parentheses.
2. * /
Multiplication and division symbols.
3. +
Addition and subtraction symbols.
4. < > <= >= = <>
Relational operators. See Expressions relational_operators for
more information.
5. NOT
Logical operator.
6. AND
Logical operator.
7. OR
Logical operator.
Relational Operators Equivalent Symbols shows equivalent symbols
for the relational operators shown in the Precedence Ordering
within Expressions table.
Table 5-1 Relational Operators Equivalent Symbols
Relational Equivalent
Operator Symbol Meaning
< LT Less than
> GT Greater than
<= LE Less than or equal to
>= GE Greater than or equal to
<> NE Not equal to
In general, CDO evaluates expressions from left to right. When
an expression contains parentheses or operators, CDO evaluates
these operations first. The following list shows the order of
precedence for these symbols, from highest to lowest:
o A symbol or symbols within parentheses
o Multiplication or division symbols
o Addition or subtraction symbols
o Relational operators: LT, GT, LE, GE, EQ, NE
o NOT
o AND
o OR
In the following example, the order of precedence determines that
the first expression evaluates to 3, while the second expression
evaluates to 8.
(6 + 12)/6 = 3
6 + 12/6 = 8
In the following expression, CDO evaluates X as a value between 2
and 4 or 11 and 20.
IF (X GE 2 AND X LE 4) OR (X GE 11 AND X LE 20) THEN 1 ELSE 0
2 – value_expr
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 }
2.1 – Parameters
2.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.
2.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.
2.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.
2.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.
2.1.5 – char-string-literal
Specifies a string of printable characters. See
character_string_literals help topic for more information.
2.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.
2.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).
2.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.
2.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.
2.1.10 – numeric-literal
Specifies a value that can be expressed as a decimal number. See
numeric_literals help topic for more information.
2.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.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.
2.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.
3 – character_string_literals
A character string literal is a string of printable characters.
The maximum length of a character string is 65,536 characters.
The printable characters are:
o Uppercase alphabetic characters (A-Z)
o Lowercase alphabetic characters (a-z)
o Numerals (0-9)
o The following special characters:
! @ # $ % ^ & * ( ) - _ = + ` ~
[ ] { } ; : ' " \ | / ? > < . ,
o Any other characters that are part of the Digital
Multinational character set
o Japanese characters: Kanji, as defined by the JIS X0208:1990
standard, and Narrow Katakana, as defined by the JIS
X0201:1976 standard
You must enclose a character string literal in a pair of either
single or double quotation marks. Quotation Marks in Character
String Literals shows the valid use of quotation marks in
character string literals.
Table 5-5 Quotation Marks in Character String Literals
Character String Value
Expression Value
"JONES" JONES
'JONES' JONES
"JONES' [invalid]
"''''" ''''
"''''' [invalid]
'My name is "Lefty".' My name is "Lefty".
'My ''handle'' is "Lefty".' My 'handle' is "Lefty".
CDO usually treats uppercase and lowercase forms of the same
letter as the same character. However, it preserves the case
distinction when doing comparisons of character strings; for
example, NAME = "JONES" and NAME = "Jones" yield different
results.
o Begin and end a character string literal with the same type of
quotation mark.
o To include a quotation mark of one type in a character string
literal, enclose the literal in quotation marks of the other
type. For example, to include double quotation marks in a
character string literal, enclose the character string in
single quotation marks.
o If a quotation mark appears in a character string literal
enclosed by quotation marks of the same type, use two
consecutive quotation marks for every one you want to include
in the literal. This technique is necessary if you want to
include quotation marks of both types in a single quoted
string.
3.1 – Examples
E IN EMPLOYEES WITH LAST_NAME IN E = "Toliver"
In this example, the expression specifies the character string
literal Toliver.
4 – numeric_literals
You can use a literal as a value expression. A literal is either
a character string or a numeric literal.
Numeric literals can take the following forms:
o A decimal string consisting of digits and an optional decimal
point. The maximum length, not counting the decimal point, is
19 digits.
o A decimal number in scientific notation (E-format), consisting
of a decimal string mantissa and a signed integer exponent,
separated by the letter D (for double), E (for E-format) or Q
(for H_floating).
CDO allows you to use unary plus and minus signs in numeric
literals. Numeric literals must start and end with a numeral and
cannot include hexadecimal digits. Numeric literals in E notation
cannot include embedded spaces.
The following expressions are valid numeric literals:
+123
-3.49
0.3338889909
6.03 E+23
If you use a numeric literal to assign a value to a field or a
variable, the data types of the field or variable determine the
maximum value you can assign.
A period at the end of a data definition command line terminates
the command; therefore, you cannot use a decimal point to
terminate a number if you want to include more data definition
clauses in the statement.
If you want to include more data definition clauses, include a
zero after the decimal point, or place the value expression in
parentheses:
COMPUTED BY X * 2.0
COMPUTED BY (X * 2.)
4.1 – Examples
S IN SALARY_HISTORY WITH SALARY_AMOUNT IN S > 40000
In this example, the expression specifies the numeric literal
40000.
5 – conditional_expr
Format
{ value-expr1 [CASE_SENSITIVE] operator value-expr2 }
{ condition-clause }
{ containing-clause }
{ }
{ matching-clause }
{ missing-clause }
{ starting-with-clause }
{ }
5.1 – Parameters
5.1.1 – value-exprN
N = value-expr1 or
N = value-expr2
Specifies a value. A value expression can consist of any of
the following: character string literals, numeric literals, or
arithmetic, concatenated, or statistical expressions. If either
value expression in a condition evaluates to null, the entire
condition evaluates to null.
5.1.2 – operator
Specifies a mathematical relational operator. See the
mathematical relational operators in relational_operators help topic.
5.1.3 – condition-clause
{ ALPHABETIC }
{ ALPHABETIC_LOWER }
{ ALPHABETIC_UPPER }
{ EMPTY_FIELD }
{ FULL_FIELD }
field-expr { NUMERIC }
{ NOT ALPHABETIC }
{ NOT ALPHABETIC_LOWER }
{ NOT ALPHABETIC_UPPER }
{ NOT EMPTY_FIELD }
{ NOT FULL_FIELD }
{ NOT NUMERIC }
Specifies whether a field expression satisfies the specified
condition.
The product using CDO evaluates a condition clause as true if
the field expression satisfies the condition specified. The
field expression specifies the name of a field in the database,
consisting of a field name and a directory name or context
variable.
When you use the keyword NOT, the product using CDO evaluates
the clause as true if the field expression does not satisfy this
condition.
5.1.4 – containing-clause
value-expr1 CASE_SENSITIVE { CONTINING } value-expr2
{ NOT CONTAINING }
Specifies whether a value expression contains a second value
expression. This operation is not case sensitive unless you
specify the CASE_SENSITIVE keyword.
When you use the keyword NOT, the product using CDO evaluates the
clause as true if the first string expression does not contain
the string that the second string expression specifies.
5.1.5 – matching-clause
value-expr CASE_SENSITIVE { MATCHING } match-expr
{ NOT MATCHING }
Specifies a relational clause that tests for substring matches.
By using wildcard characters, you can specify the position of the
substring. This operation is not case sensitive.
The product using CDO evaluates a MATCHING clause as true if
match expression, the second expression, matches a substring of
the first expression. Specify the match expression in quotation
marks.
When you use the keyword NOT, the product using CDO evaluates
the clause as true if the second expression does not match a
substring of the first value expression.
5.1.6 – missing-clause
{ field-expr } { MISSING }
{ record-expr } { NOT MISSING }
Specifies whether a field or record expression is null. The
product using CDO evaluates a MISSING clause as true if the
record or field expression is null.
Specifies the name of a field or record in the database,
consisting of a directory name or a field or record name and a
context variable.
When you use the keyword NOT, the product using CDO evaluates the
clause as true if the record or field expression is not null.
5.1.7 – starting-with-clause
value-expr1 CASE_SENSITIVE { STARTING WITH } value-expr2
{ NOT STARTING WITH }
Specifies whether the first characters of a value expression
match the characters of a second value expression. This operation
is case sensitive.
The product using CDO evaluates a STARTING WITH clause as true
if the first characters of the first string expression match the
characters in the second string expression.
When you use the keyword NOT, the product using CDO evaluates the
clause as true if the first string does not contain the string
that the second string expression specifies.
If either value expression in a condition evaluates to null, the
condition evaluates to null.
5.2 – Description
A conditional expression, sometimes called a Boolean expression,
represents the relationship between two value expressions. A
conditional expression returns a value of true, false, or null
(missing).
Conditional expressions consist of value expressions and
relational or logical operators.
You can use conditional expressions in CDO as objects for the
WITH clause or VALID IF clause of the record selection expression
or the VALID IF clause in field definitions.
6 – relational_operators
Relational operators specify the relationship of value
expressions and perform the following kinds of operations:
o Compare a value with a range
o Match a pattern
o Test for missing fields
6.1 – Description
CDO uses mathematical relational operators and pattern testing
relational operators in its conditional expressions.
Mathematical relational operators are symbols that allow you
to compare values. Pattern testing relational operators are
keywords that allow you to test for a pattern of values. Unlike
the mathematical relational operators, each pattern testing
relational operator has its own unique syntax.
Pattern Testing Relational Operators lists the pattern testing
relational operators.
Table 5-6 Pattern Testing Relational Operators
Clause Relational Operation
BETWEEN True if the first value expression is less than or
equal to the second value expression and greater
than or equal to the third value expression.
CONTAINING True if the string specified by the second string
expression is found within the string specified
by the first string expression. CONTAINING is not
case sensitive.
MATCHING True if the second value expression matches a
substring of the first value expression. MATCHING
is not case sensitive. It uses the following
wildcard characters:
o Asterisk (*)- Matches any string of zero or
more characters
o Percent sign (%)-Matches any single character
in that position
STARTING True if the first characters of the first string
WITH expression match the second string expression.
STARTING WITH is case sensitive.
The logical operators AND, OR, and NOT let you compare two or
more conditional expressions and optionally reverse the value of
a conditional expression. The result of using a logical operator
is another conditional expression.
Mathematical Relational Operators lists the mathematical
relational operators. These operators allow you to compare
values. In all cases, if either value expression in a conditional
expression is null, the value of the entire condition is null.
Table 5-7 Mathematical Relational Operators
Permitted
Symbols Relational Operation
EQ or = True if the two value expressions are equal.
NE or <> True if the two value expressions are not equal.
GT or > True if the first value expression is greater than the
second.
GE or >= True if the first value expression is greater than or
equal to the second.
LT or < True if the first value expression is less than the
second.
LE or <= True if the first value expression is less than or
equal to the second.
Use either the alphabetic symbol or the mathematical symbol
from the Permitted Symbols column, but do not use both when you
specify a relational operator.
See the documentation for the languages and products that use
the repository to determine how that product evaluates character
string literals. In some cases, character string literals are
compared according to the ASCII collating sequence. Under ASCII,
lowercase letters have a greater value than uppercase letters,
and the letters near the beginning of the alphabet have a lesser
value than those near the end.
For products that compare character string literals according to
the ASCII collating sequence, the following statements are true:
o a > A
o a < z
o A < Z
To determine how CDO conditional expressions linked by logical
operators are evaluated, see the documentation for the product
that will be evaluating the conditional expression.
See the documentation for languages and products that use the
repository to determine how they evaluate character string
literals.
CAUTION
The NOT operator applies to conditional expressions. Do not
use the NOT operator and an equal sign instead of the NE
or <> relational operators. The following statement is not
valid:
WITH SALARY_AMOUNT IN S NOT = 30000
Use one of the following alternatives:
WITH NOT (SALARY_AMOUNT IN S = 30000)
WITH SALARY_AMOUNT IN S NE 30000
WITH SALARY_AMOUNT IN S <> 30000
6.2 – Examples
1.LAST_NAME CONTAINING "ith"
LAST_NAME NOT CONTAINING "son"
In this example, if LAST_NAME has the string ith, CDO evaluates
the CONTAINING clause as true; if LAST_NAME does not contain
the string son, CDO evaluates the CONTAINING clause as true.
2.SALARY_AMOUNT IN SH > 50000
In this example, the conditional expression is true if the
value in the SALARY_AMOUNT field is greater than 50,000.
3.NOT SALARY_AMOUNT IN SH < 50000
In this example, the conditional expression is true if the
value in the SALARY_AMOUNT field is less than 50,000.
4.DEFINE FIELD SEX
VALID IF (SEX CASE_SENSITIVE EQ "M") OR (SEX CASE_SENSITIVE EQ "F").
In this example, the DEFINE FIELD uses a case sensitive
relational operator in the VALID IF clause to test whether the
code to be entered in the field SEX is M or F. The conditional
expression is true if the value for the field SEX is M or F
(not m or f).
5.LAST_NAME MATCHING "*ON"
In this example, the conditional expression is true if the
field LAST_NAME has ON as the last two letters. You can use
this expression to find all records with LAST_NAME fields
satisfying this condition.
6.LAST_NAME IN FULL_NAME IN E MISSING
In this example, the conditional expression is true if the
LAST_NAME field in the FULL_NAME record of the EMPLOYEES
relation is missing.
7.LAST_NAME IN FULL_NAME IN E ALPHABETIC
In this example, CDO evaluates the field expression as true
when the LAST_NAME field from the FULL_NAME record of the
EMPLOYEES relation is alphabetic.
8.SALARY_AMOUNT NOT MISSING
In this example, the conditional expression is true if the
SALARY_AMOUNT field has a value that is not null.
9.SALARY_AMOUNT MATCHING "4*"
This example shows the matching clause used with numeric data
types. In this example, the matching clause finds all the
salaries that start with the number 4.
10SALARY_AMOUNT BETWEEN 40000 AND 49999
This example finds all salaries in a range by using the BETWEEN
clause.
7 – record_sel_expr
Format
[first-clause] relation-clause [cross-clause] [with-clause] [reduced-clause] [sort-clause]
7.1 – Parameters
7.1.1 – first-clause
FIRST value-expr
Specifies how many records are in the record stream formed by
the record selection expression (RSE). The value expression,
value-expr, is a symbol or string of symbols used to calculate
a value. The value expression in a FIRST clause must either
be a positive number or a value expression that evaluates to a
positive integer. The record stream cannot contain more records
than the number specified by the value expression.
7.1.2 – relation-clause
context-var IN relation-name
Declares context variables for a record stream or a loop. The
context variable specifies a temporary name that identifies the
record stream to the product evaluating the clause. You then use
the context variable to refer to fields from that relation. The
relation name specifies the relation from which CDO will take the
records in the record stream.
7.1.3 – cross-clause
{ CROSS relation-clause } ...
Allows you to combine records from two or more record streams.
You join these records in combinations based on the relationship
between the values of fields in each record stream. This
combination is called a relational join.
The relation clause declares context variables for a record
stream or loop.
7.1.4 – with-clause
WITH cond-expr
Allows you to specify conditions that must be true for CDO to
include a record in a record stream. You specify any conditional
expression in this clause.
The record becomes part of a record stream only when its
values satisfy the conditions you specified in the conditional
expression (that is, only when the conditional expression is
true). If the conditional expression evaluates to false or
missing for a record, that record is not included in the record
stream.
7.1.5 – reduced-clause
REDUCED TO value-expr ,...
Allows you to eliminate duplicate values for fields in a record
stream and to group the records in a relation according to
unique field values. However, only using the REDUCED clause
does not guarantee the sort order within groups and the results
are unpredictable. To ensure specific order, use the SORTED BY
clause.
The value expression, value-expr, specifies a symbol or string of
symbols used to calculate a value.
7.1.6 – sort-clause
{ }
SORTED BY { [ ASCENDING ] value-expr } ,...
{ [ DESCENDING ] }
{ }
Allows you to sort the records in the record stream by the values
of specific fields. The value expression, or sort key, determines
the order in which CDO returns records. The default for an
initial sort key is ASCENDING. The default for subsequent keys
is the specification for the initial key.
The value expression, value-expr, specifies the value to sort by;
this value is called the sort key.
7.2 – Description
A record selection expression (RSE) is a clause that products use
at run time to include specific records for processing. The RSE
defines the conditions that individual records must meet before
CDO includes them in a record stream.
7.3 – Examples
1.FIRST 5 C IN CURRENT_SALARY
SORTED BY DESCENDING SALARY_AMOUNT IN C
You can use FIRST and SORTED BY clauses to find the maximum
values for a field. In this example, the FIRST clause finds the
five highest paid employees.
2.E IN EMPLOYEES
In this example, the RELATION clause retrieves all records from
the EMPLOYEES relation.
3.COUNT OF E IN EMPLOYEES WITH STATE IN E = "NY"
In this example, the RELATION clause declares E as the context
variable for the stream of records from the EMPLOYEES relation.
4.E IN EMPLOYEES CROSS JH IN JOB_HISTORY
WITH EMP_ID IN E = EMP_ID IN JH
In this example, the CROSS clause finds all employees for whom
data is stored in the JOB_HISTORY relation.
5.E IN EMPLOYEES CROSS J IN JOBS
In this example, the CROSS clause retrieves information on all
employees and their job descriptions.
6.E IN EMPLOYEES WITH JOB_CODE IN E = "R"
In this example, the WITH clause returns all employees whose
JOB_CODE equals R.
7.REDUCED TO JOB_CODE IN J
In this example, the REDUCED clause lists all active job codes
once.
8.EMPLOYEES SORTED BY EMPLOYEE_ID IN E
In this clause, the SORTED BY clause sorts EMPLOYEES by
EMPLOYEE_ID.
9.SORTED BY DESCENDING STATUS_CODE IN E
ASCENDING LAST_NAME IN E, EMPLOYEE_ID IN E
In this example, the SORTED BY clause sorts first by STATUS_
CODE in descending order. Within each STATUS_CODE group, SORTED
BY sorts by LAST_NAME in ascending order. Finally, within
groups of employees with the same last name, SORTED BY sorts
by EMPLOYEE_ID. The order for this last sort is also ascending,
because it adopts the order from the previous sort key.