1 Value_Expressions Value expressions specify values that DEC DATATRIEVE uses when executing statements. 2 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. 3 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. 3 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. 3 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. 3 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. 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. 3 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. 3 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. 2 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 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 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 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> 2 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> 2 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. 2 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. 2 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 3 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 2 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> 2 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> 2 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. 3 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 4 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. 3 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 4 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. 2 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] 3 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. 2 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] 3 Arguments value-expression Is a DEC DATATRIEVE value expression rse Is a record selection expression