Value expressions specify values that DEC DATATRIEVE uses when
executing statements.
1 – Literals
The simplest way to specify a value is with a literal. A literal
is either a character string enclosed in quotation marks or a
number.
1.1 – Character String Literals
A character string literal is a string of printing characters
up to 253 characters long. The maximum size for an input line
in DEC DATATRIEVE is 255 characters, but in character string
literals, two of those characters are used for the quotation
marks. The printing characters consist of the uppercase and
lowercase letters, numbers, and the following special characters:
! @ # $ % ^ & * ( ) - _ = + ` [
{ ] } ~ ; : ' " \ | , < . > / ?
To type a literal on more than one line, enter a hyphen
immediately before pressing the RETURN key. DEC DATATRIEVE strips
the hyphen from that part of the character string literal and
waits for you to complete the literal by typing the closing
quotation mark. As long as the total number of characters in
the literal does not exceed 253, you can use any number of
continuation characters between the quotation marks.
Although DEC DATATRIEVE usually converts all lowercase letters
of your input to uppercase, it preserves lowercase letters in
character string literals. Because the case of the character
string literals is preserved, comparisons using these literals
are case sensitive.
1.2 – Numeric Literals
A numeric literal is a string of digits that DEC DATATRIEVE
interprets as a decimal number. A numeric literal may contain a
decimal point and up to 31 digits. The decimal point is optional
and is not counted in the maximum number of digits.
A numeric literal can begin with a decimal point. Thus, for
example, .5 is a valid numeric literal.
A numeric literal cannot end with a decimal point. For example,
123. is not a valid numeric, but 123.0 is.
If you use a numeric literal to assign a value to a field or a
variable, the data type of the field or variable controls the
maximum value you can assign.
2 – Qualified Field Names
You can use elementary field names, virtual field names, and
group field names as value expressions.
2.1 – Elementary and REDEFINES Field Names
The value specified by an elementary field name is the value
stored in a field of a record. If the field name you use refers
to a REDEFINES field, the value associated with the name is
determined by the clauses that define the REDEFINES field in
the record definition.
2.2 – COMPUTED BY Fields
COMPUTED BY fields are virtual fields. DEC DATATRIEVE does not
store the value of a COMPUTED BY field in a record. That value
is calculated every time you refer explicitly or implicitly to a
COMPUTED BY field.
The COMPUTED BY clause includes a value expression. The value
specified by the virtual field is associated with the value
expression in the COMPUTED BY clause.
2.3 – Group Field Names
When you use group field names in assignment statements, the
value of the group field depends on the type of assignment you
specify. For example:
o group-field-name-1 = group-field-name-2
In this type of assignment, the value of group-field-name-2
includes the values of all elementary fields, all REDEFINES
fields, and all COMPUTED BY fields. The assignment of values
is made on the basis of similar field names. For field
names in group-field-1 that match names in group-field-2,
DEC DATATRIEVE assigns the values in group-field-2 to the
appropriate fields in group-field-1.
DEC DATATRIEVE ignores any fields in group-field-2 whose
names do not match any field names in group-field-1. For any
elementary field in group-field-1 whose name matches none of
the field names in group-field-2, DEC DATATRIEVE assigns the
MISSING or DEFAULT value if one is defined for the field, or a
value of 0 if the field is numeric, or a blank if the field is
alphabetic or alphanumeric.
DEC DATATRIEVE stores no values in any COMPUTED BY or
REDEFINES fields in group-field-1, regardless of any matches
between the names of those fields and fields of any type in
group-field-2.
The values of the elementary, REDEFINES, and COMPUTED BY
fields associated with group-field-name-2 are the values
stored in or associated with the fields that constitute group-
field-2 of a record.
o elementary-field-name = group-field-name
Because of problems that can arise from conflicting data
types, assignments of this type are not recommended.
If you make an assignment of this type, the value of the group
field is the same value displayed when you enter a DISPLAY
group-field-name statement. The value is the concatenation of
the values in the elementary fields that constitute the group
field. The REDEFINES and COMPUTED BY fields are ignored. You
can reduce conflicts of data types by using an alphanumeric
PICTURE string in the definition of the elementary field.
2.4 – Query Names
If the record definition contains a query name for a field, you
can use the query name exactly as you use the associated field
name.
2.5 – Qualifying Field Names
To clarify the context for recognizing names and associating
values with those names, you can qualify field names with several
optional elements:
o Context variables
Distinguish between fields in different record streams.
o Collection names
Distinguish between fields in selected records in different
collections. You can use a collection name to modify a field
from a selected record only.
o Domain names
Distinguish between fields that have the same field name but
are associated with different domains.
o Record names
Distinguish between fields that have the same field name but
are contained in different records.
o Group field names
Distinguish between fields that have the same field name but
are contained in different group fields.
Use the following format to specify field names:
[ collection-name ]
[ context-variable ][domain-name] [group-field-name] [...] field-
[ ]
name
For duplicate field names, you must specify whatever option or
combination of options is needed to make the name unique.
3 – Variables
The DECLARE statement defines global and local variables for use
as value expressions. When DEC DATATRIEVE initializes a variable,
it assigns the variable the MISSING VALUE or DEFAULT VALUE if one
is specified in the DECLARE statement. DEC DATATRIEVE assigns a
value of zero to numeric variables and a space to alphabetic and
alphanumeric variables if no MISSING VALUE or DEFAULT VALUE is
specified.
3.1 – Global Variables
You can define global variables only with DECLARE statements
entered in response to the DTR> prompt of DEC DATATRIEVE command
level.
You can use a global variable as a value expression in any DEC
DATATRIEVE statement. Unless you define a global variable with
a COMPUTED BY clause, the global variable retains the value you
assign to it until you either assign it a new value or release it
with the RELEASE command.
The value of a global variable defined with a COMPUTED BY clause
depends on the value expression that controls the computation.
For example, you can declare the value of the variable to be 1.2
times the price of a boat in the YACHTS domain. The value of the
variable changes according to the value of the PRICE field for
different records:
DTR> READY YACHTS
DTR> DECLARE VAR COMPUTED BY PRICE * 1.2.
DTR> FOR FIRST 5 YACHTS PRINT VAR USING $$$,$$$.99
VAR
$44,341.20
$21,480.00
$33,000.00
$22,320.00
$11,874.00
DTR>
3.2 – Local Variables
You can define local variables with DECLARE statements entered in
BEGIN-END and THEN statements.
A local variable is released as soon as DEC DATATRIEVE completes
the execution of the clause or statement in which it was
declared. Although a local variable stays in effect for
subsequent statements of the compound statement in which it is
declared, it has no meaning in any outer statements containing
that compound statement.
3.3 – Null Values and Variables
DEC DATATRIEVE supports relational databases null values in
variables. Therefore, DEC DATATRIEVE has the following behavior:
o When transferring a missing value from a DEC DATATRIEVE
variable to a relational field, the field receives a null
value.
DTR> DECLARE VAR PIC X(10) MISSING VALUE "77".
DTR> PRINT VAR
VAR
77
DTR> STORE EMPLOYEE_MANAGER_TABLE USING EMP_ID = VAR
DTR> PRINT EMPLOYEE_MANAGER_TABLE
EMP_ID MGR_ID
NULL NULL
DTR>
o When transferring a null value from a relational field to a
DEC DATATRIEVE variable, the variable receives either its
missing value (if a MISSING VALUE clause is specified in the
variable definition), or a null value (if the variable does
not contain a MISSING VALUE clause).
DTR> DECLARE EMPLOYEE PIC X(10) MISSING VALUE 8999.
DTR> FOR X IN EMPLOYEE_MANAGER_TABLE
CON> EMPLOYEE = EMP_ID
DTR> PRINT EMPLOYEE
EMPLOYEE
8999
DTR> DECLARE MANAGER PIC X(10).
DTR> FOR X IN EMPLOYEE_MANAGER_TABLE
CON> MANAGER = MGR_ID
DTR> PRINT MANAGER
MANAGER
NULL
DTR>
4 – Date Value Expressions
If you define a field or a variable with a USAGE DATE clause,
then you can assign a value with one of the four DEC DATATRIEVE
date value expressions:
o "TODAY" returns the value of the current system date.
o "NOW" returns the value of the current system date and time.
o "YESTERDAY" returns the value of one day before the current
date.
o "TOMORROW" returns the value of one day after the current
date.
Note that "NOW" is the only value expression that returns the
time as well as the date. You can use the function FN$DATE to
assign a date field a time that is not current.
You can add or subtract dates. For example, you might want
to know how many days you have to complete a project. Define
variables for today's date and the project date. Then subtract
today's date from the project due date:
DTR> DECLARE T USAGE DATE.
DTR> T = "TODAY"
DTR> DECLARE PROJECT_DUE DATE.
DTR> PROJECT_DUE = "21-AUG-90"
DTR> PRINT (PROJECT_DUE - T)
112
DTR>
DEC DATATRIEVE indicates that the project is due in 112 days.
To use these value expressions, you must assign the DATE data
type to the field or variable. Otherwise, DEC DATATRIEVE treats
the expression as a character string literal. For example:
DTR> PRINT "TODAY"
TODAY
DTR>
DEC DATATRIEVE returns the value "TODAY" because the quoted
expression is not associated with a variable or field of the
DATE data type. However, if you supply an edit string containing
date edit string characters, DEC DATATRIEVE returns the current
system date in the form specified by the edit string:
DTR> PRINT "TODAY" USING DD-MMM-YY
18-Jan-90
DTR>
5 – Prompting Value Expressions
If you want DEC DATATRIEVE to prompt you for a value, use a
prompting value expression. This feature is especially useful
in a procedure because it allows you to use a different value
each time you invoke the procedure. DEC DATATRIEVE recognizes two
types of prompting expressions: the *.prompt and the **.prompt.
The *.prompt value expression has the following format:
*."prompt-name"
The prompt name is a character string literal. If the prompt name
contains no blanks and conforms to the rules for DEC DATATRIEVE
names, you do not have to enclose the literal in quotation marks.
If the prompt name contains blanks or does not conform to those
rules, you must enclose it in quotation marks.
6 – Values from a Table
You can use a value stored in a dictionary table or a domain
table anywhere the syntax of a DEC DATATRIEVE statement allows a
value expression.
The format for retrieving a value from a dictionary table or a
domain table is as follows:
value-expression VIA table-name
If the value expression you specify is stored as a code string in
the table you specify, the value of the entire expression is the
corresponding translation string in the table.
If the table contains an ELSE clause and the value expression you
specify does not match any code string in the table, the value
of the entire expression is the translation string stored in the
ELSE clause of the table.
If the table contains no ELSE clause and the value expression
you specify does not match any code string in the table, DEC
DATATRIEVE displays the following message:
Value not found from record or table.
See the DEC DATATRIEVE User's Guide for more information about
the creation and use of dictionary tables and domain tables.
7 – Statistical Expressions
Statistical expressions compute values based on a value
expression evaluated for each record in a record stream.
Format
To specify the average, maximum, minimum, standard deviation, or
total:
{ AVERAGE }
{ MAX }
{ MIN } value-expression [OF rse]
{ STD_DEV }
{ }
{ TOTAL }
To specify the count:
COUNT [OF rse]
To specify the running count:
RUNNING COUNT
To specify the running total:
RUNNING TOTAL value-expression
7.1 – Arguments
value-expression
Is a DEC DATATRIEVE value expression on which the statistical
function operates.
OF rse
Is a record selection expression you can use to form a record
stream of the records to which the statistical function applies.
Values Derived with Statistical Functions shows the operations
performed by the DEC DATATRIEVE statistical functions.
Table 1 Values Derived with Statistical Functions
Function Value of Function
AVERAGE The average value of the value expression
COUNT The number of records in the CURRENT collection
or in a specified collection or record stream
MAX The largest value of the value expression
MIN The smallest value of the value expression
RUNNING COUNT The running count of the evaluations of the PRINT
statement
RUNNING TOTAL The running total of the value expression for
each evaluation of the PRINT statement
STD_DEV The standard deviation of the value expression
TOTAL The total value of the value expression
8 – Arithmetic Expressions
An arithmetic expression consists of value expressions and
arithmetic operators. The value expressions must all be numeric.
You can use an arithmetic expression anywhere the syntax of a DEC
DATATRIEVE statement allows a value expression.
DEC DATATRIEVE provides four arithmetic operators. These are +
(addition), - (subtraction or negation), * (multiplication), and
/ (division).
You do not have to use spaces to separate arithmetic operators
from value expressions, except in one case: if a DEC DATATRIEVE
name precedes a minus sign, you must put a space before the minus
sign. Otherwise, DEC DATATRIEVE interprets the minus sign as a
hyphen and converts it to an underscore:
DTR> DECLARE X PIC 99.
DTR> X = 8
DTR> PRINT X-1
"X_1" is undefined or used out of context
DTR> PRINT 1-X
-7
DTR>
You can use parentheses to control the order in which DEC
DATATRIEVE performs arithmetic operations. DEC DATATRIEVE
follows the normal rules of precedence when evaluating arithmetic
expressions:
1. DEC DATATRIEVE first evaluates any value expressions in
parentheses.
2. DEC DATATRIEVE then performs multiplications and divisions
from left to right in the arithmetic expression.
3. Finally, DEC DATATRIEVE performs additions and subtractions
from left to right in the arithmetic expression.
The following examples show how DEC DATATRIEVE evaluates
arithmetic expressions:
DTR> PRINT (6 * 7) + 5
47
DTR> PRINT 6 * (7 + 5)
72
DTR> PRINT 6 + 7 * 5
41
DTR> PRINT 12 - 6 * 2
0
DTR> PRINT 5 + 10 / 2
10
DTR>
DEC DATATRIEVE performs arithmetic operations on relational
databases null values according to SQL/Services[TM] standards.
For example: an arithmetic or string operation generates null
when one of the operands is null.
DTR> FOR X IN EMPLOYEE_MANAGER_TABLE PRINT EMP_ID
EMP_ID
10
15
30
NULL
NULL
DTR> FOR X IN EMPLOYEE_MANAGER_TABLE PRINT EMP_ID + 1000
1010
1015
1030
NULL
NULL
DTR>
9 – Concatenated Expressions
DEC DATATRIEVE allows you to join value expressions to form a
concatenated expression. You can use a concatenated expression
anywhere the syntax of a DEC DATATRIEVE statement allows a
character string literal. When DEC DATATRIEVE concatenates
value expressions, it converts their values to character string
literals.
DEC DATATRIEVE provides three types of concatenated expressions:
o value-expression | value-expression
o value-expression || value-expression
o value-expression ||| value expression
In each case, DEC DATATRIEVE converts the values of each value
expression to a character string literal and joins the literals
to form a longer literal. The differences among the three forms
of concatenated expression lie in the way they treat trailing
spaces of the first literal, and whether they add any spaces
between the literals:
o A single bar leaves the literals as they are. For example:
"ABC"|"DEF" "ABC "|"DEF" "ABC"|" DEF" "ABC "|" DEF"
ABCDEF ABC DEF ABC DEF ABC DEF
o A double bar suppresses trailing spaces of the first literal
and does nothing to the leading spaces of the second literal.
For example:
"ABC"||"DEF" "ABC "||"DEF" "ABC"||" DEF" "ABC "||" DEF"
ABCDEF ABCDEF ABC DEF ABC DEF
o A triple bar suppresses trailing spaces of the first literal,
inserts one space, and does nothing to the leading spaces of
the second literal. For example:
"ABC"|||"DEF" "ABC "|||"DEF" "ABC"|||" DEF" "ABC "|||" DEF"
ABC DEF ABC DEF ABC DEF ABC DEF
You can use concatenated expressions for assigning values to
lengthy fields or variables. Concatenated expressions provide
the only method for assigning values to fields or variables whose
lengths exceed 255 characters. The following example combines
the T edit string, *.prompt value expressions, and character
string literals to assign a value to a long variable. You can use
similar assignment statements in the USING clauses of the STORE
and MODIFY statements:
DTR> DECLARE STR PIC X(300) EDIT_STRING IS T(50).
DTR> STR = *.L1|*.L2|*.L3|*.L4|*.L5
Enter L1: This string contains the first part of a long character
Enter L2: string. This string is so long that you can't use just
Enter L3: one character string literal to assign a value to it.
Enter L4: You need concatenated expressions to increase the length
Enter L5: of this string beyond the limit of 255 characters.
DTR> PRINT STR
STR
This string contains the first part of a long
character string. This string is so long that you
can't use just one character string literal to
assign a value to it. You need concatenated
expressions to increase the length of this string
beyond the limit of 255 characters.
DTR>
10 – Conditional Value Expressions
The CHOICE and IF-THEN-ELSE value expressions return a value
based on the evaluation of one or more Boolean expressions. These
value expressions are useful when you need to assign values that
depend on certain conditions. They can be used in any statement
that accepts value expressions, as well as in COMPUTED BY clauses
for variables or field definitions.
10.1 – CHOICE Value Expression
Returns one of a series of values depending on the evaluation of
a series of conditional (Boolean) expressions.
Format
CHOICE [OF]
boolean-expression-1 [THEN] value-1
[boolean-expression-2 [THEN] value-2]
. . .
. . .
. . .
ELSE value-n
END_CHOICE
10.1.1 – Arguments
CHOICE
Marks the beginning of a CHOICE value expression.
OF
Is an optional word used to clarify syntax.
boolean-expression
Is a Boolean expression.
THEN
Is an optional language element you can use to clarify syntax.
value
Is the value returned by DEC DATATRIEVE if the corresponding
Boolean expression evaluates to true.
ELSE value-n
Is the value returned by DEC DATATRIEVE if all the Boolean
expressions evaluate to false.
10.2 – IF THEN ELSE Value Expression
Returns one of two values depending on the evaluation of a
conditional (Boolean) expression.
Format
IF boolean-expression [THEN] value-1 ELSE value-2
10.2.1 – Arguments
boolean-expression
Is a Boolean expression.
THEN
Is an optional language element you can use to clarify syntax.
value-1
Is the value returned by DEC DATATRIEVE if the Boolean expression
evaluates to true.
ELSE value-2
Is the value returned by DEC DATATRIEVE if the Boolean expression
evaluates to false.
11 – FORMAT Value Expression
Specifies a value that is formatted according to the default edit
string or the edit string you indicate.
Format
FORMAT value-expression [USING edit-string]
11.1 – Arguments
value-expression
Is a field name or other DEC DATATRIEVE value expression
specifying a value that DEC DATATRIEVE uses when executing
statements.
edit-string
Is one or more edit string characters that determines the value
of the value expression. For more information, see the EDIT_
STRING clause.
12 – FROM Value Expression
Allows you to perform complex retrievals of records from one
or more domains or collections. You can include a FROM value
expression in the Boolean of the RSE that forms the collection
or record stream. The optional OTHERWISE clause lets you specify
an alternative DEC DATATRIEVE value expression if an initial
search of data based on an RSE produces no records. If the number
of records in a record stream equates to zero, the alternative
specified by the OTHERWISE clause is returned.
Format
value-expression FROM rse [OTHERWISE value-expression]
12.1 – Arguments
value-expression
Is a DEC DATATRIEVE value expression
rse
Is a record selection expression