1 Value_expr A symbol or string of symbols that you use to calculate a value. When you use a value expression in a statement, Oracle Rdb calculates the value associated with the expression and uses that value when executing the statement. Any value expression can be enclosed in parentheses or preceded by a minus sign. 2 arithmetic_expression An arithmetic expression combines arithmetic operators with: numeric values, numeric host language variables, and/or numeric database fields. When you use an arithmetic expression in a statement, Oracle Rdb 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. If either operand of an arithmetic expression is a null value, the resulting value is also null. (B)0arith-expr = qwq> numeric-value qwqqwqq> + qqwqqwq> numeric-value qwqqq> tq> numeric-host-var qu tqq> - qqu tq> numeric-host-var qu mq> numeric-db-field qj tqq> * qqu mq> numeric-db-field qj mqq> / qqj 2 concatenated_expression A value expression that combines two other value expressions by joining the second to the end of the first. Concatenated expressions uses the concatenate operator (|). You can combine value expressions of any kind, including numeric expressions, string expressions, and literals. You can use concatenated expressions to simulate group fields and edit strings. Format (B)0concat-expr = qqq> value-expr qqq> | qqq> value-expr qqq> 3 Examples Example 1 Use a concatenated expression as a field in a view: DEFINE VIEW MAIL OF E IN EMPLOYEES. NAME COMPUTED BY E.FIRST_NAME | ' ' | E.MIDDLE_INITIAL | ' ' | E.LAST_NAME. E.ADDRESS_DATA_1. E.CITY. E.POSTAL_CODE. END MAIL VIEW. This statement creates a field for the view by combining the three name fields from the EMPLOYEES relation. Example 2 Use a concatenated expression to simulate an edit string: DEFINE VIEW BUCKS OF S IN SALARY_HISTORY. EMPLOYEE_ID. SALARY_START. SALARY_END. SAL COMPUTED BY "$"|S.SALARY_AMOUNT. END. When you display the fields of BUCKS, a dollar sign appears in front of the salary amount. 2 database_field --> context-var . field-name --> The name of a field in the database that consists of a context variable and a field name. A context variable is a temporary name that you associate with a relation. You define a context variable in a record selection expression. For example: FOR E IN EMPLOYEES PRINT E.LAST_NAME | | | | | | +-----+-----+ | | | context relation database variable name field 2 database_key A pointer that indicates a specific record in the database. You can use the database key to retrieve a record in a FETCH statement or in a record selection expression. Database keys are intended primarily for use in host language programs. (B)0db-key = qqq> context-var qq> . qqq> RDB$DB_KEY qqq> 2 missing_values A missing value for a field in a relation has no value associated with it. The missing value is an attribute of a field rather than a value stored in a field. The null flag refers to the information that Oracle Rdb maintains about a field to determine whether or not it is null, or missing. Each field in the database has information associated with it using the null flag to mark the field as either missing or not missing. Value expressions can evaluate to a value or to missing. Conditional expressions can evaluate to true, false, or missing. Missing information also affects the evaluation of statistical functions, sorting, and projections. If the source of the assignment is a database field and that database field has a missing value defined for it, then its missing value is used. Otherwise, the missing value is determined by the data type. For fixed-length strings, the string is blank filled. For variable-length strings, the length is set to zero. All numeric data types are set to zero. For the DATE data type, the missing value of the VMS zero date is used. This represents the date 17-NOV-1858 00:00:00.00. When you assign a value to a database field, the null flag for that field is set according to the value in the assignment statement. If the value of the assignment is the missing value defined for that field, the null flag is set for that field. Otherwise, the null flag is not set. If no missing value is defined, the null flag for that field is not set. When you define a field, choose a missing value for that field that will never occur as an actual value for the field. If you change the definition of the missing value for a field, you change the incoming value that sets the null flag and you change the value produced when reading a field with the null flag set. Oracle Rdb evaluates the expression RDB$MISSING to determine the missing value for a field. There are two ways to declare that a field is missing: o Omit the field in the STORE statement. When you omit the field from the STORE statement, Oracle Rdb VMS marks the field as null. o Explicitly store the value associated with RDB$MISSING for the field, or use RDB$MISSING to signal the field's value to Oracle Rdb. Oracle Rdb does not actually store what you specify; rather, it stores nothing and marks the field's value as missing or null. If you know the missing value for a field, you can include this value in the STORE or MODIFY statement. For example, assume you want to make the ADDRESS_DATA field missing for a particular employee. The definition for ADDRESS_DATA is as follows: DEFINE FIELD ADDRESS_DATA DATA_TYPE IS TEXT SIZE IS 20 MISSING_VALUE IS 'Not available'. To make the field missing, you use a MODIFY statement: FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = "00175" MODIFY E USING E.ADDRESS_DATA = 'Not available' END_MODIFY END_FOR When this statement executes, Oracle Rdb marks the field as null; and when you retrieve the field, Oracle Rdb returns the value "Not available", the missing value. Note, however, that to find all the records in which the ADDRESS_DATA field is marked as null, use the MISSING operator in a conditional expression, as in the following example: FOR E IN EMPLOYEES WITH E.ADDRESS_DATA MISSING PRINT E.* END_FOR If instead of the MISSING operator, you use the missing value in the conditional expression, Oracle Rdb displays nothing. The following query does not find records in which the ADDRESS_DATA field is marked as null: FOR E IN EMPLOYEES WITH E.ADDRESS_DATA = "Not available" PRINT E.* END_FOR If you want to make a field missing and you do not know the literal missing value, you can use the expression RDB$MISSING, which returns the missing value. For example, you might want to write general update programs that do not depend on a particular missing value. You can use RDB$MISSING instead of an explicit value in a STORE or MODIFY statement. ________________________Note ________________________ In order to use RDB$MISSING, you must have defined a missing value explicitly for the field in the DEFINE FIELD statement. If you have not, RDB$MISSING is undefined for that field. _____________________________________________________ You can define a missing value that is longer than the length specified for the field in the field definition. However, when the missing value is displayed, it will be truncated to the length specified for the field in the field definition. In the following example, the missing value "Long" in the field SEX_FIELD is truncated to one character (the length specified for the field in the definition of SEX_FIELD). RDO> ! RDO> ! Define field SEX_FIELD: RDO> ! RDO> DEFINE FIELD SEX_FIELD cont> DATATYPE IS TEXT 1 cont> MISSING_VALUE "Long". RDO> ! RDO> ! Define relation TEST_RELATION: RDO> ! RDO> DEFINE RELATION TEST_RELATION. cont> SEX_FIELD. cont> EMPLOYEE_ID BASED ON ID_NUMBER. cont> END RELATION. RDO> ! RDO> ! Store a single record in TEST_RELATION, providing a value RDO> ! for EMPLOYEE_ID, but not for SEX_FIELD. This creates a RDO> ! missing value for SEX_FIELD in the record: RDO> ! RDO> STORE TR IN TEST_RELATION USING cont> TR.EMPLOYEE_ID = "00175"; cont> END_STORE RDO> ! RDO> ! The missing value in SEX_FIELD appears as one character RDO> ! when the record is displayed: RDO> ! RDO> FOR TR IN TEST_RELATION cont> PRINT TR.* cont> END_FOR SEX_FIELD EMPLOYEE_ID L 00175 3 Format (B)0missing-value = q> RDB$MISSING q> ( qwq> context-var . field-name qqqqqqqqqqwq ) q> tq> rel-name . field-name qqqqqqqqqqqqqu mq> db_handle . rel-name . field-name qj 3 Usage_Notes o The default value for a field ("Rdb default" in displays by the SQL statement SHOW TABLE table-name) is not the same as the missing value that you can specify with RDO. If you do not specify a value for a field (column) with a default value, the default value is actually stored in the database. This is true whether you are using RDO or SQL. An RDO missing value is not actually stored in the database. If you use RDO to specify a missing value for a field, that missing value is displayed by RDO when the field has no value stored and the internal null flag is set. SQL does not recognize any missing value specified by RDO; if the field has no value stored and the null flag is set, then SQL displays "NULL" for the column, regardless of whether you specified any missing value with RDO. One implication of the way in which Oracle Rdb handles default values is that if you change the default value for a column, it has no effect on any existing data in the database; that is, rows stored with columns containing the "old" default values are not changed. By contrast, changing the missing value does change what is displayed by RDO-based applications for columns that have no value stored and that have the null flag set. o The value of RDB$MISSING is set at compile time. If the missing value for a field is changed, then sources that contain references to RDB$MISSING for that field still use the old missing value. To correct this problem, all sources that contain a reference to RDB$MISSING on the field must be recompiled. 3 Examples Example 1 The following example uses RDB$MISSING in a STORE statement: . . . * * If the date in the input file contains zeros, * use the GET statement with RDB$MISSING to retrieve * the missing value and assign it to a variable. * IF END-DATE = '000000' THEN &RDB& GET END-DATE = RDB$MISSING(SALARY_HISTORY.SALARY_END) &RDB& END_GET END-IF. * * Store the field values in the relation. * If the date in the input file contained zeros, * the missing value is passed to Oracle Rdb, and * the SALARY_END field is marked as missing. * &RDB& STORE S IN SALARY_HISTORY USING &RDB& S.EMPLOYEE_ID = EMP-ID; &RDB& S.SALARY_START = START-DATE; &RDB& S.SALARY_END = END-DATE; &RDB& S.SALARY_AMOUNT = SALARY; &RDB& END_STORE This program fragment shows how your program might test for a missing field in an input file and make the field missing in the database. This program assumes that a missing value has been defined for the field. The program does not need to know what the missing value is, and the program needs only one STORE statement. 2 first_from The FIRST FROM expression works in two steps: o It forms the record stream as specified by the record selection expression. o If at least one record matches the RSE, Oracle Rdb uses the values stored in the first record of the record stream to evaluate the value expression. Format (B)0first-from-expr qqq> FIRST qqq> value-expr qqq> FROM qqq> rse qqq> 2 host_variable A host or substitution variable, used to communicate with a calling (host) program. A host language program declares a host language variable and then assigns database values to it. Host language variables are not used in RDO. You can also use host language variables as names to represent databases and database elements. These names are called handles. 3 database_handle A variable name declared as a longword in a host language program. You can use the database handle in several statements and clauses to identify a database. 3 transaction_handle A host language variable you associate with a transaction. If you do not declare the transaction handle explicitly, Oracle Rdb attaches an internal identifier to the transaction. The ability to declare a transaction handle is provided for compatibility with other database products and future releases of Oracle Rdb. 3 request_handle A host language variable you associate with a compiled request. 2 literal A character string literal, numeric literal, or date literal. You can use a literal as a value expression. For example: FOR E IN EMPLOYEES WITH E.LAST_NAME = "Toliver" PRINT E.EMPLOYEE_ID ------- END_FOR | string literal FOR S IN SALARY_HISTORY WITH S.SALARY_AMOUNT > 40000 PRINT S.EMPLOYEE_ID ----- END_FOR | numeric literal FOR S IN SALARY_HISTORY WITH S.SALARY_START = "9-APR-1980" PRINT S.EMPLOYEE_ID ---------- END_FOR | date literal 2 statistical_expression Calculates a value based on a value expression for every record in a record stream. When you use a statistical expression (except for COUNT), you specify a value expression and a record selection expression (RSE). Oracle Rdb first evaluates the value expression for each record in the record stream formed by the RSE. Then it calculates a single value based on all the results of the first step. Statistical expressions are also called aggregate expressions. For more information on value expressions and record selection expressions, ask for HELP on Value_expr and RSE. 3 Format (B)0statistical-expr = qqqqqqqwqq> MAX qqqqqwqq> value-expr qqwqq> OF qq> rse qq> tqq> MIN qqqqqu x tqq> TOTAL qqqu x tqq> AVERAGE qj x mqq> COUNT qqqqqqqqqqqqqqqqqqqqqj 4 MAX --> MAX --> value-expr --> OF --> rse --> Find the highest paid employee in the company: START_TRANSACTION READ_ONLY FOR SAL IN CURRENT_SALARY WITH SAL.SALARY_AMOUNT = MAX CURR.SALARY_AMOUNT OF CURR IN CURRENT_SALARY PRINT SAL.EMPLOYEE_ID, SAL.LAST_NAME, SAL.SALARY_AMOUNT END_FOR COMMIT 4 MIN --> MIN --> value-expr --> OF --> rse --> Find the job title with the smallest minimum salary: FOR J IN JOBS WITH J.MINIMUM_SALARY = MIN JM.MINIMUM_SALARY OF JM IN JOBS PRINT J.JOB_TITLE, J.MINIMUM_SALARY END_FOR 4 TOTAL --> TOTAL --> value-expr --> OF --> rse --> Assign the total payroll for the company to a host language variable: &RDB& START_TRANSACTION READ_ONLY &RDB& GET TOTAL-SALARY = TOTAL CH.SALARY_AMOUNT &RDB& OF CH IN CURRENT_SALARY &RDB& END_GET &RDB& COMMIT 4 AVERAGE --> AVERAGE --> value-expr --> OF --> rse --> Display the average salary of all the employees whose salaries exceed $50,000: PRINT AVERAGE CS.SALARY_AMOUNT OF CS IN CURRENT_SALARY WITH CS.SALARY_AMOUNT GT 50000 4 COUNT --> COUNT --> OF --> rse --> The following COBOL code fragment finds out the number of employees who live in a particular state, specified by the host language variable STATE: ACCEPT STATE. &RDB& GET STATE-COUNT = COUNT OF E IN EMPLOYEES &RDB& WITH E.STATE = STATE &RDB& END_GET DISPLAY "Number of employees in ", STATE, " is ", STATE-COUNT. 3 More If you invoke multiple databases in the RDO interface and declare an aggregate expression, Oracle Rdb returns an %RDB-E-INVALID_BLR error. For example: RDO> INVOKE DATABASE FEE = FILENAME USER1:[STUDENT_FEES]STUDENTDB RDO> INVOKE DATABASE STA = FILENAME USER2:[STUDENT_FEES]STATS RDO> RDO> START_TRANSACTION ON FEE USING cont> (READ_ONLY RESERVING FEE.TRANS FOR SHARED READ) AND cont> ON STA USING (READ_WRITE RESERVING STA.STATDATA FOR cont> EXCLUSIVE WRITE) RDO> RDO> FOR TX IN FEE.TRANS SORTED BY TX.SNO, TX.SESS, TX.TYPE cont> REDUCED TO TX.SNO, TX.SESS, TX.TYPE cont> WITH TX.SESS = "91S" cont> STORE SX IN STA.STATDATA USING cont> SX.SNO = TX.SNO; cont> SX.SESS = TX.SESS; cont> SX.TYPE = TX.TYPE; cont> SX.AMOUNT = (TOTAL T1.AMOUNT OF T1 IN FEE.TRANS WITH cont> T1.SNO = TX.SNO AND cont> T1.SESS = TX.SESS AND cont> T1.TYPE = TX.TYPE); cont> END_STORE cont> END_FOR %RDB-E-INVALID_BLR, request BLR is incorrect at offset 172 RDO> ROLLBACK;