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.
1 – 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)0[marith-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)0[mconcat-expr =
qqq> value-expr qqq> | qqq> value-expr qqq>
2.1 – 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.
3 – 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
4 – 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)0[mdb-key =
qqq> context-var qq> . qqq> [4mRDB$DB_KEY[m qqq>
5 – 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
5.1 – Format
(B)0[mmissing-value =
q> [4mRDB$MISSING[m q> ( qwq> context-var . field-name qqqqqqqqqqwq ) q>
tq> rel-name . field-name qqqqqqqqqqqqqu
mq> db_handle . rel-name . field-name qj
5.2 – 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.
5.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.
6 – 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)0[mfirst-from-expr
qqq> [4mFIRST[m qqq> value-expr qqq> [4mFROM[m qqq> rse qqq>
7 – 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.
7.1 – 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.
7.2 – 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.
7.3 – request_handle
A host language variable you associate with a compiled request.
8 – 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
9 – 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.
9.1 – Format
(B)0[mstatistical-expr =
qqqqqqqwqq> [4mMAX[m qqqqqwqq> value-expr qqwqq> [4mOF[m qq> rse qq>
tqq> [4mMIN[m qqqqqu x
tqq> [4mTOTAL[m qqqu x
tqq> [4mAVERAGE[m qj x
mqq> [4mCOUNT[m qqqqqqqqqqqqqqqqqqqqqj
9.1.1 – 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
9.1.2 – 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
9.1.3 – 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
9.1.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
9.1.5 – 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.
9.2 – 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;