1 Expressions 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. 2 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 = EQ 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 } 3 Parameters 4 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. 4 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. 4 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. 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. 4 char-string-literal Specifies a string of printable characters. See character_string_literals help topic for more information. 4 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. 4 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). 4 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. 4 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. 4 numeric-literal Specifies a value that can be expressed as a decimal number. See numeric_literals help topic for more information. 4 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" 3 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. 2 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 Examples E IN EMPLOYEES WITH LAST_NAME IN E = "Toliver" In this example, the expression specifies the character string literal Toliver. 2 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.) 3 Examples S IN SALARY_HISTORY WITH SALARY_AMOUNT IN S > 40000 In this example, the expression specifies the numeric literal 40000. 2 conditional_expr Format { value-expr1 [CASE_SENSITIVE] operator value-expr2 } { condition-clause } { containing-clause } { } { matching-clause } { missing-clause } { starting-with-clause } { } 3 Parameters 4 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. 4 operator Specifies a mathematical relational operator. See the mathematical relational operators in relational_operators help topic. 4 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. 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. 4 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. 4 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. 4 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. 3 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. 2 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 3 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 3 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. 2 record_sel_expr Format [first-clause] relation-clause [cross-clause] [with-clause] [reduced-clause] [sort-clause] 3 Parameters 4 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. 4 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. 4 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. 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. 4 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. 4 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. 3 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. 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.