Datatrieve is an interactive language and report-writing tool for managing information organized as collections of interrelated data (databases). You use Datatrieve to query and report on a database. Datatrieve can access three types of databases:

Datatrieve is a fourth-generation language. Its syntax is more similar to English than that of COBOL and BASIC, and it has a strong non-procedural aspect. It executes commands as you type them, and you can often simply tell Datatrieve what information you want by name, instead of specifying how to obtain that information.

Datatrieve lets you define records and store record definitions separately from the procedures that use them. You can then write any number of procedures that use the records you have defined, without redefining the record each time.

Datatrieve also lets you create data definitions, called view domains, that can access either a subset of the fields in one data file or a combination of fields from more than one file. View domains can help you reduce the number of statements that you have to write when retrieving data.

Datatrieve provides the same data storage capabilities that you have with other languages. It can store and retrieve data using existing data files of most types that are supported by RMS. It can also create sequential and multikey indexed files, but not relative files.

Datatrieve also handles other common language functions automatically, without the need for language statements. For instance, Datatrieve:

As a result, you can save many lines of code, get applications running quickly, and have code that is more readable than languages such as COBOL or BASIC.

Using the Datatrieve Call Interface, you can include Datatrieve functions in a program written in another language. The Call Interface is used most often in two ways:

Datatrieve does not give you all the options available with other languages. For example:

Examples in this manual show you how to create your own file-structured databases and how to access data stored in Oracle DBMS and relational databases. The term database is sometimes used in other documentation to refer only to data stored by database management systems such as Oracle DBMS or the Oracle relational database products. For the remainder of this document, database is used to refer to data stored in files.

During an interactive Datatrieve session, you control Datatrieve operations by entering a series of commands and statements:

Besides this difference in function, commands and statements also differ in structure:

A compound statement is two or more statements you combine in a BEGIN-END statement or a THEN statement. You can use a compound statement anywhere you can use a simple statement. Datatrieve executes each statement of a compound statement in consecutive order.

Every Datatrieve command and statement consists of one or more of the following elements:

You can perform complex or repetitive tasks by nesting statements within other statements. With the REPEAT, FOR, and WHILE statements, you can form repeating loops. With the IF-THEN-ELSE and CHOICE statements, you can do conditional transfers or branching.

The VSI Datatrieve Reference Manual contains complete descriptions of the commands and statements of Datatrieve.

Most applications of Datatrieve involve sequences of commands and statements that recur regularly.

You can use OpenVMS command files in Datatrieve in much the same way you use Datatrieve procedures. There are three major differences between the two.

Every element of a Datatrieve command or statement must be constructed of characters from the Datatrieve character set. The Sort Order Appendix in the VSI Datatrieve Reference Manual lists the characters in the Datatrieve character set.

Most keywords are elements of commands or statements. In this manual, Datatrieve keywords are printed in uppercase letters.

Keywords are restricted to the positions shown in syntax formats of commands and statements. Do not use keywords as names of domains, records, fields, dictionary tables, domain tables, views, databases, database instances, collections, procedures, or variables.

You can create a unique set of custom-tailored keywords. You can define these keywords to be synonyms for Datatrieve keywords or to create your own.

See the DECLARE SYNONYM section in the VSI Datatrieve Reference Manual for more information about these methods of defining synonyms.

A Datatrieve name is a character string used to identify one of the following items:

A Datatrieve name can be from 1 through 31 characters long and can contain letters, digits, hyphens (-), underscores (_) and dollar signs ($). Datatrieve names, however, must conform to the following set of restrictions:

You can continue a name from one input line to another by typing a hyphen (-) and pressing Return. The following list shows some valid Datatrieve names:

• TOTAL_SALARY
• YACHTS
• PRICE_PER_POUND
• YEAR-TO-DATE_EARNINGS_FOR_1980

The following list shows some invalid Datatrieve names:

• TOTAL (Duplicates a keyword)
• 1980_EARNINGS (Does not begin with a letter)
• PRICE-PER-POUND($/LB) (Contains illegal characters)
• YEAR-TO-DATE_EARNINGS_FOR_FY_1980 (Contains too many characters)

Datatrieve has two termination characters, the semicolon (;) and the carriage return (Return), and a continuation character, the hyphen (-).

A terminator signals the end of a command or statement. The formal terminator in Datatrieve is the semicolon (;). You can enter several commands on the same input line if you separate each from the next with a semicolon. If you enter more than one command on an input line, Datatrieve does not begin processing those commands until you press the Return key.

You can enter SET NO SEMICOLON to turn off the semicolon requirement.

You can terminate commands and statements, except the DEFINE and DELETE commands and the DECLARE statement, by pressing the Return key when the syntax of the command or statement is complete.

If the SET PROMPT command is in effect and you press the Return key before the syntax of a statement or command is complete, Datatrieve prompts you for the next element in the syntax.

You can make sure you will be able to continue your command by pressing the Return key at the following points in your command line:

You can use the hyphen (-) at the end of an input line to continue your command or statement on the next input line. Datatrieve does not check the syntax of your input until you press the Return key at the end of a line that does not end with a hyphen. Therefore, if the command line you are entering ends in a hyphen, you must end the command line with a semicolon.

If an input line ends with a complete word, enter a space before typing the hyphen and pressing the Return key. If you do not enter a space after the complete word or at the beginning of the next line, Datatrieve considers the characters at the end of the first line and those at the beginning of the next to be one string of characters.

If you have to change input lines in the middle of a name, keyword, or any other character string, you must use a hyphen to shift your input to the next line. The maximum number of characters in an input line extended by hyphens is 255.

You can include a comment in an input line by preceding the comment with an exclamation point (!) and ending it by pressing the Return key. The comment can include any characters on your keyboard except escape and control characters.

You can also use the exclamation point in procedures and command files to document their functions. When you invoke a procedure that contains comments, Datatrieve suppresses the comments. The comments in procedures, however, are stored in the data dictionary as part of the procedure definition.

When you invoke a command file, Datatrieve displays the comment lines in the command file if the SET VERIFY command is in effect. To suppress the display, enter a SET NO VERIFY or SET NOVERIFY command. See the section on the SET command in the VSI Datatrieve Reference Manual for more information.

The current Datatrieve object is the last collection established using the FIND command, no matter what you have readied in the meantime.

The current collection is known as CURRENT, so this name can be used in place of the name allotted (if there is one). Statements expecting a collection name will assume CURRENT if nothing else is provided.

The Datatrieve HELP command provides on-line information about the use of Datatrieve commands, statements, and language elements.

When you enter HELP or a question mark (?) in response to the DTR> prompt, Datatrieve displays a list of topics to choose from.

If you already know which topic you want, you can enter it on the same line as the HELP command. For example, if you want information on defining a domain, enter the HELP command as follows:

DTR> HELP DEFINE DOMAIN

When you are in the help facility, press the PF2 key on the auxiliary keypad or enter VIDEO as the topic. This displays information on the screen-oriented Help facility and explains how to scan the Datatrieve help messages. You can move through the text by using the arrow keys:

You can type a question mark (?) to redisplay the current help topics.

If you enter HELP HELP, Datatrieve displays more detailed information on the HELP command.

When the topic you have selected contains subtopics, Datatrieve asks if you want information on a subtopic.

If you are at one of the subtopic levels of help, you can press the Return key to move up a level. The prompt displayed tells you at what level of help you are.

Enter Ctrl/Z to exit from help. This returns you to the Datatrieve prompt DTR>.

DTR> FIND PERSONNEL
"PERSONNEL" is not a readied source, collection, or list.
DTR> HELP ERROR
"PERSONNEL" is not a readied source, collection, or list.

ERROR

  NOTDOMAIN

        EXPLANATION:

        The source for a Datatrieve collection must be a
        readied domain, relation, or DBMS record; a collection;
        or a list.

        USER ACTION:

        Check that you have spelled all names correctly. Ready the
        appropriate record source, if necessary, and reenter
        the statement.

Topic?  Ctrl/Z

DTR>

Guide Mode is a self-documenting aid available whenever you are at the DTR> prompt. Guide Mode has two functions:

To enter Guide Mode, type:

DTR> SET GUIDE

As you enter each word of a command or statement, you can still enter the word as you usually do. As soon as you have typed enough letters to uniquely identify a command or statement, you can press the space bar and Guide Mode completes the entry for you and prompts you for the next word. At the end of a line, press the Return key and Guide Mode goes to the next line.

When Guide Mode is waiting for your input, you can press the question mark (?) key. Guide Mode then displays a list of all the words you can use at that point.

Datatrieve also supplies you with Advanced Guide Mode which functions like Guide Mode except that more words are available as prompts. To enter this type of Guide Mode, enter the following command:

DTR> SET GUIDE ADVANCED

The choice of words provided by Advanced Guide Mode is made when Datatrieve is installed. By default, the PLOT and REPORT statements, and the use of a colon (:) to invoke a procedure, are available in Advanced Guide Mode only. The following words are available at both levels by default:

The easiest way to learn about Guide Mode is to use it. You may find it particularly helpful when you are starting to use Datatrieve. Experiment with it and use it the way it helps you the most.

Within Datatrieve, you can use one of the following editors:

Note that you can also edit from the DCL and at CDD/Repository levels. At the DCL level, you can use your choice of editors depending on what is installed on your system. At the CDD/Repository level, you can use the Dictionary Management Utility (DMU) or the Common Dictionary Operator (CDO) to edit dictionary objects. See the documentation for your particular editor and for CDD/Repository for further information.

You can enter the EDIT command within Datatrieve without specifying a dictionary path name. Datatrieve then invokes your default editor and loads the previous command or statement into the main text buffer of the editor.

This feature is most useful if the previous command or statement contains an error. The following list shows how you can use EDIT to correct such an error:

In the following example, assume you did not want to include the argument BEAM:

DTR> FIND YACHTS WITH BUILDER = "GRAMPIAN"
DTR> PRINT ALL LOA,
CON> BEAM,
CON> DISP/2000 ("DISP/2000")
DTR>

To correct the mistake, type the EDIT command without an argument at the DTR> prompt; this recalls all the lines of the previous PRINT statement. Next, edit the statement, eliminating the BEAM argument. After you exit from the editor, Datatrieve executes the corrected statement.

You can use the EDIT command and the arrow keys to recall and edit the previous line. However, when you recall non-hyphenated commands or statements that are continued over more than one line, the EDIT command and the arrow keys function differently:

If you are working in a DECwindows environment, you can use the Last Command item of the Edit menu of the main application window to edit your most recently executed command. If you do, Datatrieve loads the command or statement into its default editing window.

DTR> EDIT CDD$TOP.DTR$LIB.DEMO.YACHTS

DTR> EDIT ALL RECORDS, DOMAINS

DTR> EDIT YACHTS_CDO RECOVER;

DTR> EDIT CDD$TOP.DTR$LIB.DEMO.YACHTS
       .
       .
       .! System failure
       .
       .
DTR> EDIT CDD$TOP.DTR$LIB.DEMO.YACHTS RECOVER

DTR> EDIT ALL DOMAINS, RECORDS

With the DECwindows interface, you can only use the DECTPU or LSE editor (you cannot use the EDT editor). You can invoke the editor in any of the following ways:

You can then use the editor to edit dictionary objects or the previous command or statement.

After you complete your editing session, you can exit from the editor or you can retain your DECTPU or LSE editing window for future editing sessions, thus avoiding the overhead of activating the editing window each time. To retain the editing window, perform the following steps:

The next time you invoke the editor, perform the following steps:

If you accidentally click on the LSE or DECTPU window before choosing Open Selected, you will break the connection between Datatrieve and the editing window. To reestablish the connection, you must reset input focus on the Datatrieve or Dictionary Navigator window, and reinvoke the editor by reentering your EDIT command.

While you are editing using LSE or DECTPU, you can set input focus on your Datatrieve main application window and enter commands. This does not affect your editing session.

Before you use Datatrieve for the first time, create an OpenVMS subdirectory for your Datatrieve data files. Next, run the NEWUSER program. This program performs the following tasks:

To run the NEWUSER program, make sure you are using the OpenVMS subdirectory you just created then enter the following command:

$ @DTR$LIBRARY:NEWUSER

The program responds with the following information:

NEWUSER helps new users to get started with DATATRIEVE. It gives
you the necessary files to perform the introductory examples
in the VSI DATATRIEVE User Guide and VSI DATATRIEVE Reference Manual.

    NEWUSER is working... It will take a few minutes.
    All data copied successfully.

    The following commands have been defined for you but you will need to
    add them to your LOGIN.COM file for the next time you log in:

$       define/process cdd$default "cdd$top.dtr$users.user"
$       define dtr$edit editor
$       define decw$display node::0

user is your username
editor is your choice of EDT, LSE, or TPU editor
node is your node-name

If you need help, see the person responsible for DATATRIEVE on your system.

The results you get when you run the NEWUSER program may differ slightly from those in the previous example. If the display shows that NEWUSER has run successfully, add the indicated line to your LOGIN.COM file.

When you display records, you might find that the fields from each record do not all fit on one display line on your terminal screen. The result is that some fields wrap to the next line and that headers for some fields either do not appear at all or are inserted in available space between other headers. A display like this is not very attractive and often difficult to read.

If you are interested in seeing only a subset of the fields in a record, you can try listing the names of the fields you want to see following the PRINT command. If these fit on one line, your problem is solved. If they do not, or if you want to see the entire record, see the following sections, which discuss some other things you can try.

DTR> FN$WIDTH (132)
DTR> SET COLUMNS_PAGE=132

DTR> FIND EMPLOYEES WITH EMPLOYEE_ID = "00181"
[1 record found]
DTR> SELECT
DTR> PRINT

                                             ADDRESS
SOCIAL   LAST        FIRST
 ID      NAME        NAME        INIT         DATA       ZIP  SEX
SECURITY

00181 Reynolds       Louis        E     Apartment 78C
63 Derry Rd.            Milton                    NH   03851  M  393  98  1984
12/11/52
DTR> LIST

EMPLOYEE_Id     : 00181
LAST_NAME       : Reynolds
FIRST_NAME      : Louis
MIDDLE_INITIAL  : E
ADDRESS_DATA    : Apartment 78C
STREET          : 63 Derry Rd.
TOWN            : Milton
STATE           : NH
ZIP             : 03851
SEX             : M
SOCIAL_SECURITY : 393 98 1984
BIRTHDAY        : 12/11/52

DTR>

DTR> SHOW PRINT_EMP
PROCEDURE PRINT_EMP
BEGIN
  PRINT "_____________________________________________________________________-
___________"
  PRINT EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL
  PRINT "_____________________________________________________________________-
___________"
  PRINT ADDRESS_DATA, STREET, TOWN, STATE, ZIP
  PRINT "_____________________________________________________________________-
___________"
  PRINT SEX, SOCIAL_SECURITY, BIRTHDAY
  PRINT "_____________________________________________________________________-
___________"
END
END_PROCEDURE

DTR> FIND EMPLOYEES WITH EMPLOYEE_ID = "00181"
[1 record found]
DTR> SELECT
DTR> PRINT

                                        ADDRESS                SOCIAL   
 ID     LAST         FIRST      INIT         DATA       ZIP  SEX  SECURITY
        NAME         NAME

00181 Reynolds       Louis        E   Apartment 78C
63 Derry Rd.             Hudson                  NH    03851  M  393 98 1984
12/11/52
DTR> PRINT_EMP
_______________________________________________________________________________

 ID     LAST         FIRST      INIT
        NAME         NAME

00181 Reynolds       Louis        E
_______________________________________________________________________________

      ADDRESS
       DATA                     STREET                 TOWN      STATE   ZIP

Apartment 78C          63 Derry Rd.             Hudson            NH    03851
_______________________________________________________________________________

      SOCIAL
SEX  SECURITY     BIRTHDAY
M   393 98 1984   12/11/52
_______________________________________________________________________________

DTR>

DTR> SHOW CONCATENATE
PROCEDURE CONCATENATE
  BEGIN
  PRINT SKIP
  PRINT EMPLOYEE_ID|||FIRST_NAME|||MIDDLE_INITIAL|||LAST_NAME,
  PRINT SKIP
  PRINT ADDRESS_DATA||" "|STREET||", "|TOWN||",
  PRINT "|STATE|||ZIP,
  PRINT SKIP
  PRINT SEX (-), SOCIAL_SECURITY (-) USING XXX_XX_XXXX,
  PRINT BIRTHDAY (-)
  END
END_PROCEDURE

DTR> FOR FIRST 3 EMPLOYEES :CONCATENATE

00164 Alvin A Toliver

 146 Parnell Place, Chocorua, NH 03817

M 763-08-0064 3/28/47

00165 Terry D Smith

 120 Tenby Dr., Chocorua, NH 03817

M 179-97-8016 5/15/54
00166 Rick Dietrich

 19 Union Square, Boscawen, NH 03301

M 902-87-8080 3/20/54

DTR>

DTR> DECLARE A PIC X(80).
DTR> DECLARE B PIC X(80).
DTR> DECLARE C PIC X(80).
DTR> DECLARE D PIC X(80).
DTR> DECLARE E PIC X(80).
DTR> !
DTR> PRINT *.A|||_*.B|||_*.C|||_*.D USING T(40)
Enter A: When DATATRIEVE joins the values in A, B, C, D,
Enter B: and E, it suppresses any trailing spaces in
Enter C: A, B, C, and D and inserts one space. It
Enter D: displays their combined values using up to 40
Enter E: characters per line and without breaking words.
When DATATRIEVE joins the values in A,
B, C, D, and E, it suppresses any
trailing spaces in A, B, C, and D and
inserts one space. It displays their
combined values using up to 40
characters per line and without breaking
words.
DTR>

For new users, Datatrieve provides a bundled CBT package, which explains the basic concepts required both to use Datatrieve and to understand the terminology used in the documentation. Before running the training, the following symbol definitions must be added to your LOGIN.COM:

EASY :== $EASY$PROGRAM:SOLORPT.EXE
LOADDRAW :== $EASY$PROGRAM:LOADDRAW.EXE

Then run the following command file:

$ @EASY$PROGRAM:SETUP.COM

To run the training, enter the following command:

$ EASY

The course is entirely self-explanatory, so no further instruction is needed here.

After you decide what fields you want to associate with a domain, you can create a record definition to describe them. There are several reasons why you might want to know the explicit way to define a record:

A record definition consists of one or more field definitions. Each field definition describes the field and its relationship to other fields in the record. Every field definition contains at least three parts:

Most field definitions also contain one or more field definition clauses.

The relationship between the fields in the record is made explicit by the level numbers assigned to the fields in the record definition. These numbers help to determine the field levels.

01 BOAT
     03 TYPE
          06 MANUFACTURER
          06 MODEL
     03 SPECIFICATIONS
          06 RIG
          06 LENGTH_OVER_ALL
          06 DISPLACEMENT
          06 BEAM
          06 PRICE

When you display data, Datatrieve uses the names you choose for the elementary fields in the record definition as default column headers for the stored values. If you segment the field name with underscores or hyphens, Datatrieve automatically uses multiple lines for the column header. This way, each segment in the name appears on a separate line in the display.

You can change the default column headers by adding a QUERY_HEADER clause to your elementary field definition. Example 2.1, ''Level Numbers in the YACHT Record Definition'' contains several examples of a QUERY_HEADER clause. The keyword IS is optional. You must enclose the header you select in quotation marks; use a slash (/) to indicate that the following header segment should appear on the next line, for example "EMP"/"ID".

As long as your field names are descriptive of the data in the field, the main reason you want to add a QUERY_HEADER clause to the record definition is to optimize use of line space in your display. Some descriptive field names are longer than the values in the field. In Example 2.1, ''Level Numbers in the YACHT Record Definition'', MIDDLE_INITIAL is an example of such a field.

The following example illustrates how several fields from EMPLOYEES_REC would display without the QUERY_HEADER clauses in Example 2.1, ''Level Numbers in the YACHT Record Definition'':

DTR>  READY EMPLOYEES
DTR>  PRINT ID, NAME, TOWN, STATE OF FIRST 5 EMPLOYEES

EMPLOYEE      LAST        FIRST    MIDDLE
   ID         NAME        NAME     INITIAL         TOWN         STATE

 00164   Toliver        Alvin         A    Chocorua              NH
 00165   Smith          Terry         D    Chocorua              NH
 00166   Dietrich       Rick               Boscawen              NH
 00167   Kilpatrick     Janet              Marlow                NH
 00168   Nash           Norman             Meadows               NH

DTR>

Now the same display with the QUERY_HEADER clauses in Example 2.1, ''Level Numbers in the YACHT Record Definition'':

DTR>  PRINT ID, NAME, TOWN, STATE OF FIRST 5 EMPLOYEES

 EMP
 ID     LAST NAME    FIRST NAME I         TOWN         STATE

 00164 Toliver       Alvin      A Chocorua              NH
 00165 Smith         Terry      D Chocorua              NH
 00166 Dietrich      Rick         Boscawen              NH
 00167 Kilpatrick    Janet        Marlow                NH
 00168 Nash          Norman       Meadows               NH

DTR>

This section explains how you tell Datatrieve about storage criteria; that is, what kind of characters are stored in a field and the maximum number of characters allowed in that field.

Every time you define an elementary field in your record definition, you must specify either a PICTURE (PIC, for short) or USAGE clause to tell Datatrieve what kind of characters are stored in the field and how many characters can fit.

SMITH-JONES

ARCO, INC.

TEA-FOR-2 CATERING

28/MAR/1946

MAR 28 1946

March 28, 1946

3/28/46

05 NET_SALARY  COMPUTED BY GROSS_SALARY - DEDUCTIONS.

10 STATE_NAME     COMPUTED BY STATE VIA STATES_TABLE.

2.3.5. Using COMPUTED BY Fields

You can also use COMPUTED BY fields to compute a fiscal quarter from a date value. The following examples use the domain CURRENT_SALES. The record includes a field for the salesperson’s ID, the date of the sale, and the amount of the sale. The figure below illustrates the structure of the record:

The record definition is as follows:

DTR> SHOW CURRENT_REC
RECORD CURRENT_REC USING
01 CURRENT_REC.
   03 ID   PIC IS 9(5).
   03 SALES_DATE   USAGE DATE.
   03 AMOUNT    PIC IS 9(5)V99
                EDIT_STRING IS $$$,$$$.99.
   03 QTR COMPUTED BY
         (FORMAT SALES_DATE USING NN) VIA QTR_TABLE
         EDIT_STRING IS "Q"9.
;
DTR>

The QTR field calculates the fiscal quarter from the date field SALES_DATE through a dictionary table.

The FORMAT value expression in QTR returns the numerical value for the month of SALES_DATE. Datatrieve evaluates the COMPUTED BY clause, looking up this value in a table and finding the numerical value for the fiscal quarter.

Datatrieve then displays this value, preceding the quarter number with the letter Q. The table QTR_TABLE is defined as follows:

DTR> SHOW QTR_TABLE
TABLE QTR_TABLE
     QUERY_HEADER IS "QTR"
     EDIT_STRING IS 9
       1  :  3
       2  :  3
       3  :  3
       4  :  4
       5  :  4
       6  :  4
       7  :  1
       8  :  1
       9  :  1
      10  :  2
      11  :  2
      12  :  2
END_TABLE

The preceding table assumes that the first quarter begins on July 1, the second on September 1, and so on.

The CHOICE or IF-THEN-ELSE value expressions increase the flexibility of COMPUTED BY fields because you can assign values based on conditional tests. You might want to display the sales amounts for each quarter in a separate column. You could define the following four virtual fields for the sales of different quarters:

05 Q1_SALES COMPUTED BY IF QTR EQ 1 THEN AMOUNT ELSE 0.

05 Q2_SALES COMPUTED BY IF QTR EQ 2 THEN AMOUNT ELSE 0.

05 Q3_SALES COMPUTED BY IF QTR EQ 3 THEN AMOUNT ELSE 0.

05 Q4_SALES COMPUTED BY IF QTR EQ 4 THEN AMOUNT ELSE 0.

The values of the virtual fields for quarterly sales are either 0 or the sales amount, depending on the value for QTR.

You can also include a COMPUTED BY field in the record to calculate total sales based on the information in the quarterly summaries:

05 TOTAL_SALES COMPUTED BY
      (Q1_SALES + Q2_SALES + Q3_SALES + Q4_SALES).

Now you can produce the desired output by entering a SUM statement:

DTR> SHOW SUMMING
PROCEDURE SUMMING
READY CURRENT_SALES
FIND CURRENT_SALES
SUM Q1_SALES ("Q1") USING $$$$,$$$.$$ ,
    Q2_SALES ("Q2") USING $$$$,$$$.$$ ,
    Q3_SALES ("Q3") USING $$$$,$$$.$$ ,
    Q4_SALES ("Q4") USING $$$$,$$$.$$ ,
    TOTAL_SALES ("TOTAL") USING $$$$,$$$.$$ BY ID
END_PROCEDURE

DTR> :SUMMING

  ID       Q1          Q2          Q3          Q4         TOTAL
11111   $2,150.91   $2,807.11   $2,748.39   $2,389.90  $10,096.31
12345   $7,805.69   $3,801.44   $9,973.94   $8,672.99  $30,254.06
22222   $5,693.29   $3,836.24   $7,274.76   $6,325.88  $23,130.17
23456  $10,311.18   $1,447.40  $13,175.40  $11,456.87  $36,390.85
33333   $7,679.00   $6,854.45   $9,812.05   $8,532.22  $32,877.72
34567   $2,338.91  $14,294.89   $2,988.61   $2,598.79  $22,221.20
44444   $8,868.17  $10,890.45  $11,331.55   $9,853.52  $40,943.69
45678   $8,999.99  $11,339.01  $11,499.99   $9,999.99  $41,838.98
55555  $23,288.42   $1,979.92  $29,757.42  $25,876.02  $80,901.78
56789  $11,111.06  $14,197.04  $14,197.46  $12,345.62  $51,851.18
66666   $9,000.01  $21,832.99  $11,500.01  $10,000.01  $52,333.02
77777   $6,593.10  $30,463.98   $8,424.52   $7,325.67  $52,807.27
88888   $4,500.00  $38,694.00   $5,750.00   $5,000.00  $53,944.00
99999   $4,499.99  $44,249.51   $5,749.99   $4,999.99  $59,499.48
      $112,839.72 $206,688.43 $144,184.09 $125,377.47 $589,089.71

DTR>

Note that this procedure uses the SUM statement to generate totals across each row for the different ID numbers.

03 CODE_FOR_SOMETHING  PIC X(6).
03 SEGMENT_THE_CODE REDEFINES CODE_FOR_SOMETHING.
   06  FIELD1          PIC X(3).
   06  FIELD2          PIC X(3).

05 PART_NUMBER         PIC 9(10).
05 PART_NUMBER_PARTS REDEFINES PART_NUMBER.
   10 PRODUCT_GROUP    PIC 99.
   10 PRODUCT_YEAR     PIC 99.
   10 ASSEMBLY_CODE    PIC 9.
   10 SUB_ASSEMBLY     PIC 99.
   10 PART_DETAIL      PIC 999.
05 PART_NUMBER_GROUPS REDEFINES PART_NUMBER.
   10 PRODUCT_GROUP_ID PIC 9(4).
   10 PART_DETAIL_ID   PIC 9(6).

DTR> SHOW FAMILY_REC
RECORD FAMILY_REC
01 FAMILY.
   03 PARENTS.
      06 FATHER PIC X(10).
      06 MOTHER PIC X(10).
   03 NUMBER_KIDS PIC 99 EDIT_STRING IS Z9.
   03 KIDS OCCURS 0 TO 10 TIMES DEPENDING ON NUMBER_KIDS.
      06 EACH_KID.
         09 KID_NAME PIC X(10) QUERY_NAME IS KID.
         09 AGE PIC 99 EDIT_STRING IS Z9.
;
DTR>

DTR> READY FAMILIES
DTR> PRINT FIRST 3 FAMILIES

                     NUMBER    KID
  FATHER    MOTHER    KIDS     NAME    AGE

  JIM       ANN        2      URSULA    7
                              RALPH     3
  JIM       LOUISE     5       ANNE    31
                                JIM    29
                              ELLEN    26
                               DAVID   24
                              ROBERT   16
  JOHN      JULIE      2        ANN    29
                               JEAN    26

DTR>

03 KIDS OCCURS 10 TIMES.

DTR>  READY FAMILIES
DTR>  PRINT FATHER, KID_NAME OF FAMILIES
"KID_NAME" is undefined or used out of context.
DTR>  PRINT ALL FATHER, ALL KID_NAME OF KIDS OF FAMILIES

              KID
  FATHER      NAME

JIM        URSULA
           RALPH
JIM        ANNE
           JIM
           ELLEN
           DAVID
           ROBERT
JOHN       ANN
           JEAN
JOHN        Ctrl/C
^C
Execution terminated by operator.

DTR>

DTR> SHOW PETS
DOMAIN PETS USING PET_REC ON PET.DAT;
DTR> SHOW PET_REC
RECORD PET_REC
01 FAMILY.
   03 PARENTS.
      06 FATHER PIC X(10).
      06 MOTHER PIC X(10).
   03 NUMBER_KIDS PIC 99 EDIT_STRING IS Z9.
   03 KIDS OCCURS 0 TO 10 TIMES DEPENDING ON NUMBER_KIDS.
      06 EACH_KID.
         09 KID_NAME PIC X(10) QUERY_NAME IS KID.
         09 KID_AGE PIC 99 EDIT_STRING IS Z9.
         09 PET OCCURS 2 TIMES.
            13 PET_NAME   PIC X(10).
            13 PET_AGE    PIC 99.
;

DTR> READY PETS
DTR> PRINT FIRST 2 PETS

                     NUMBER     KID     KID    PET     PET
  FATHER    MOTHER    KIDS      NAME    AGE    NAME    AGE

JIM       LORAINE       2    GARY       24  POP        03
                                            SODA       04
                             SUE        23  MOUSE      03
                                            SHORTY     08
JIM       ANN           2    URSULA      7  SQUEEKY    03
                                            FRANK      07
                             RALPH       3             00
                                                       00

DTR>

You can always specify the output format of an elementary field value by including an edit string in a PRINT statement. If you want this information in a record definition, you use the EDIT_STRING clause. Example 2.1, ''Level Numbers in the YACHT Record Definition'' includes an edit string for the field SOCIAL_SECURITY (EDIT_STRING IS XXXBXXBXXXX) to display the value with a space between segments of the field. It also includes an edit string for the field BIRTHDAY to replace the Datatrieve default of DD-Mmm-YYYY (EDIT_STRING IS NN/DD/YY). This is how those fields would display without the edit string:

DTR>  READY EMPLOYEES
DTR>  PRINT NAME, SOCIAL_SECURITY,
CON>  BIRTHDAY OF FIRST 1 EMPLOYEES

                             SOCIAL
  LAST NAME    FIRST NAME I SECURITY   BIRTHDAY

Toliver        Alvin      A 763080064 28-Mar-1947

DTR>

This is how those same fields display with the edit strings in Example 2.1, ''Level Numbers in the YACHT Record Definition'':

DTR>  READY EMPLOYEES
DTR>  PRINT NAME, SOCIAL_SECURITY,
CON>  BIRTHDAY OF FIRST 1 EMPLOYEES

                              SOCIAL
  LAST NAME    FIRST NAME I  SECURITY    BIRTHDAY

Toliver        Alvin      A 763 08 0064   3/28/47

DTR>

If you do not supply an EDIT_STRING clause for a numeric field, Datatrieve uses the PIC clause to format the field value. If the PIC clause contains a V or P, Datatrieve displays the value with a decimal point in the correct position. You usually want to include an EDIT_STRING clause for numeric fields that:

Tables in the EDIT_STRING section of the VSI Datatrieve Reference Manual illustrate examples of using editing characters for text, numeric, and date fields.

Field definition clauses identify the data type of the values to be stored in the field. With few exceptions, data types are equivalent for Datatrieve, the Common Data Dictionary Data Definition Language utility (CDDL), and the Common Dictionary Operator Utility (CDO). The following table lists both sets of data types:

Note that the CDDL keyword STRUCTURE is analogous in function to Datatrieve level numbers for fields.

You cannot extract and edit a record definition defined using the VARIANT field of the CDDL unless each VARIANT field has a STRUCTURE statement. If the CDDL record definition includes a STRUCTURE field description statement for each VARIANT field, you can extract and edit the record definition. See Example 2.1, ''Level Numbers in the YACHT Record Definition'' for a sample record definition.

DTR>  DEFINE RECORD  EMPLOYEES_REC  USING
DFN> !     ^        ^              ^
DFN> ! required     name of        optional
DFN> ! keywords    definition      keyword
DFN> !
DFN>    01      EMPLOYEES_REC      .
DFN> !  ^       ^                  ^
DFN> !  level   name of            end of field
DFN> !  number  top-level field    definition
DFN> !
DFN>            05      EMPLOYEE_ID   PIC X(5)
DFN> !          ^       ^             ^
DFN> !          level   name of       field defini-
DFN> !          number  field         tion clause
DFN> !
DFN>                                   QUERY_NAME IS ID
DFN> !                                     ^
DFN> !                               field defini-
DFN> !                               tion clause
DFN> !
DFN>                                   QUERY_HEADER IS "EMP"/"ID" .
DFN> !                                     ^                     ^
DFN> !                               field defini-       end of field
DFN> !                               tion clause         definition
DFN> !
DFN>            05  EMPLOYEE_NAME             QUERY_NAME IS NAME.
DFN> !                 ^
DFN> !         This group field contains the three elementary fields
DFN> !         that follow it.
DFN> !
DFN>               10  LAST_NAME            PIC X(14)
DFN>                                        QUERY_NAME IS L-NAME
DFN>                                        QUERY_HEADER IS "LAST NAME".
DFN>               10  FIRST_NAME           PIC X(10)
DFN>                                        QUERY_NAME IS F-NAME
DFN>                                        QUERY_HEADER IS "FIRST NAME".
DFN>               10  MIDDLE_INITIAL       PIC X
DFN>                                        QUERY_NAME IS INIT
DFN>                                        QUERY_HEADER IS "I".
DFN> !             ^
DFN> !             Note that all fields subordinate to EMPLOYEE_NAME
DFN> !             have level numbers with larger values.
DFN> !
DFN>            05  EMPLOYEE_ADDRESS.
DFN>                10  ADDRESS_DATA         PIC X(20).
DFN>                10  STREET               PIC X(25).
DFN>                10  TOWN                 PIC X(20).
DFN>                10  STATE                PIC X(2).
DFN>                10  ZIP                  PIC X(5).
DFN>            05  SEX                      PIC X
DFN>                                         VALID IF SEX = "M" OR
DFN>                                         SEX = "F".
DFN>            05  SOCIAL_SECURITY          PIC X(9)
DFN>                                         QUERY_NAME IS SS
DFN>                                         EDIT_STRING XXXBXXBXXXX
DFN>                                         VALID IF SS BETWEEN
DFN>                                           "1" AND "999999999".
DFN>            05  BIRTHDAY                 USAGE DATE
DFN>                                         EDIT_STRING IS NN/DD/YY.
DFN>  ;
DTR> ! ^
DTR> ! end of record definition
DTR> !
DTR>

Many of the field definitions in this record are probably used by other applications in the company. Fields like LAST_NAME and FIRST_NAME can be used in many applications. By storing and using only one field-level definition for items with company-wide application, you can ensure data consistency throughout the company. The CDO utility of CDD/Repository offers field-level definition capability for records you want to store in a CDO format dictionary. Datatrieve lets you take advantage of this CDD/Repository feature by letting you include CDO-defined field level definitions in your Datatrieve record definition.

Datatrieve performs best if you choose key fields for indexed records wisely. This is especially important when you use the CROSS clause. Chapter 22, "Improving Datatrieve Performance" has information on key optimization.

03 DATE_IN USAGE DATE DEFAULT "TODAY".

You can define a MISSING VALUE clause to mark that no value is stored in a field. Datatrieve ignores fields containing the missing value marker when computing averages, standard deviations, and minimum and maximum values.

Numeric fields are automatically initialized to zero if no value is stored in them. It is fairly common for records to be stored in incomplete form. If a field storing a salary contains zero, for example, it usually means that the salary data has not been stored, not that the employee is working solely for fun. If you are averaging the salaries of all current employees in a given job category, you do not want records with these "empty" salary fields to affect your results. You can include the MISSING VALUE clause in the field definition to make sure that salaries equal to zero are ignored:

05  SALARY_AMOUNT                     PIC 9(6)V99
                                      EDIT_STRING IS $$$$,$$$.$$
                                      MISSING VALUE IS 0.

Do not use the DEFAULT VALUE clause along with the MISSING VALUE clause unless they specify the same value. If they specify different values, Datatrieve initializes an empty field to the default value and includes that value in statistical computations.

You can create the CDO field definitions in one of two ways:

You should note that you can only define elementary fields in CDO. If you want to make use of group fields, you use the CDO DEFINE RECORD command to create a record that includes the elementary fields that you would use in a group field definition. You can include the CDO record definition in a Datatrieve record definition as a group field.

An example in Chapter 20, "Using Datatrieve With a CDO Format Dictionary" shows a CDO_ADDRESSES domain that points to the record ADDRESSES_REC. ADDRESSES_REC uses the FROM clause of the Datatrieve DEFINE RECORD command to include the CDO-defined objects LAST_NAME, FIRST_NAME, and ADDRESS_GROUP_FIELD:

DTR> SET DICTIONARY DISK$1:[KIRK.DTR]SAMPLE
DTR> SHOW ADDRESSES_REC
RECORD ADDRESSES_REC
01 ADDRESSES_REC.
   03 FULL_NAME.
      05 FROM FIELD LAST_NAME.
      05 FROM FIELD FIRST_NAME.
      05 MIDDLE_INIT PIC X.
   03 FROM GROUP ADDRESS_GROUP_FIELD.
;
DTR>

ADDRESSES_REC is composed of two group fields: FULL_NAME and ADDRESSES_GROUP_FIELD. The group field FULL_NAME includes three elementary fields, two of which were defined using CDO. The CDO-defined fields are LAST_NAME and FIRST_NAME. You can display their CDO definitions in Datatrieve using the Datatrieve CDO command:

DTR> CDO SHOW FIELD LAST_NAME, FIRST_NAME
Definition of field LAST_NAME
|   Data type                   text size is 20 characters
Definition of field FIRST_NAME
|   Data type                   text size is 15 characters
DTR>

The field MIDDLE_INIT is not defined in CDO. It is local to Datatrieve and exists only as part of a record definition. The group field FULL_NAME consists of a mix of CDO-defined fields and the local field MIDDLE_INIT.

The group field ADDRESS_GROUP_FIELD is actually defined in CDO as a record. It contains the elementary fields displayed in the following example:

DTR> CDO SHOW RECORD ADDRESS_GROUP_FIELD
Definition of record ADDRESS_GROUP_FIELD
|    Contains field                       STREET_NUMBER
|    Contains field                       STREET
|    Contains field                       CITY
|    Contains field                       STATE
|    Contains field                       ZIP_CODE
DTR>

The terms FIELD and GROUP used in the syntax of the FROM clause are required Datatrieve keywords. The term FIELD indicates that the CDO object specified in the clause is an elementary field. The term GROUP indicates that the CDO record definition is used in Datatrieve as a group field.

Note that if you define a new version of the field or record referred to in the FROM clause, your record will still point to the original version. You must redefine your record if you want it to access the new version.

When you edit a record definition, you see the keyword REDEFINE where DEFINE used to be. The REDEFINE RECORD command follows the same rules as DEFINE RECORD. REDEFINE RECORD, however, automatically creates a new version of an existing record definition.

The DEFINE RECORD command works only when the dictionary directory contains no record definition with the specified name.

If you want to modify a record definition that is being used with an existing data file, read the section on restructuring domains in Section 4.7.3, ''Restructuring a Domain''.

When you use the Datatrieve EDIT command to edit a data definition stored in a CDD/Repository dictionary, Datatrieve extracts the CDD/Repository definition or definitions to an editing buffer. You can then edit the definition using the default editor specified by the logical DTR$EDIT.

You can use the EDIT command in Datatrieve to update a CDO format domain definition to have it point to the newest version of a particular record definition. This is particularly important because if a domain was defined with relationships, those relationships point to specific versions of dictionary objects. Relationships are not automatically propagated to new versions of objects. When a newer version of a record is created, the owner domain (if it was defined with the RELATIONSHIPS clause of the DEFINE DOMAIN command) still points to the old version of the record.

For example, the domain YACHTS_CDO owns record YACHT_CDO_REC;2. After YACHT_CDO_REC is redefined to create YACHT_CDO_REC;3, the domain YACHTS_CDO still points to the definition YACHT_CDO_REC;2. To update the relationship so that the domain YACHTS_CDO now points to YACHT_CDO_ REC;3, you must redefine YACHTS_CDO. You can update the relationship by entering the Datatrieve command EDIT YACHTS_CDO. Remove the version number before exiting the editor.

The following pages contain the "Data Requirements Study" for the sample personnel system referred to in this book.

Data Requirements Study

Personnel System

Purpose

Like any other personnel system, this one must maintain employee data, answer on-line inquiries and create reports.

System Requirements

System requirements relate to the devices that your application will be receiving data from and sending data to. System requirements also take into account whether or not your application will be receiving and sending information across a computer network:

Report Requirements

What information your database contains depends to a large extent on the reports you want it to produce. This personnel system must generate the following reports:

On-line Inquiry Requirements

On-line inquiry to a personnel database must be restricted to information that the person making the inquiry has a right to see. In this system, the following employees can access the information listed:

Database Updating Requirements

Requirements for data update include how the data is maintained and how the system ensures the data is valid. This personnel system has the following updating requirements:

At this stage of your application, you want to generate a list of the pieces of information your database should contain. There are a number of ways you can do this, but you might find it easiest to follow these steps:

The following table shows a list of fields you might start with when creating a personnel system. A number of fields would appear in more than one report. Some of them would probably never appear in the same report together. At this point, you want to know how many pieces of data you have to work with rather than how you are going to group them:

Expect that field requirements will change as you think about organizing them into domains or tables. This list of fields does not take into account, for example, special fields to indicate whether a record contains current or historical information.

After you know what fields you will need for your application, you have to decide how best to group them.

Datatrieve gives you a variety of methods to look at data stored in different locations. You should therefore pay special attention to ease of maintenance and logical grouping of fields when you put together a record.

Aim to put a field in only one place, unless you plan to use it as a link to related information stored somewhere else. Employee names, for example, are best stored in only one place. Employee ID numbers, however, probably need to be stored in several locations.

When you group fields together, consider grouping fields that contain generic data apart from fields that contain specific data. Job information, for example, can be generic (the same for each job code) or employee-specific. Generic information, such as wage class and minimum salary, can go in one domain. Employee-specific information, such as start date and review code, can go in another domain. If you keep generic data apart from specific data, you save storage space. If job entries for employees include wage class and minimum salary, values for these fields will be stored in many records when they need to be stored in only a few.

The following figure shows one way you could organize the fields in the sample personnel system. Above each grouping is the domain name that will eventually associate the record definition describing the fields with the data file that will store them:

The fields preceded by an asterisk (*) indicate fields that you can use in Datatrieve statements to link data in one domain with data in the other domains. When grouping fields into domains for your own applications, you should note the following points about pivotal fields like these:

A domain is the heart of the Datatrieve process. It performs the following functions:

After you create the record and domain definitions and define the data file, you use the domain name to access the file.

You can also create domain definitions that point to data stored on other computer systems and in Oracle DBMS and relational databases.

The following example illustrates the process for creating a domain definition. The definition relates the sample record definition from EMPLOYEES_REC with the file EMPLOYEES.DAT. The domain name EMPLOYEES identifies this relationship:

DTR> ! Set dictionary location to the directory that will store
DTR> ! the domain definition.
DTR> !
DTR> SET DICTIONARY CDD$TOP.PERSONNEL
DTR> !
DTR> ! Define the domain.
DTR> !
DTR> DEFINE DOMAIN EMPLOYEES USING
DFN> EMPLOYEES_REC ON EMPLOYEES.DAT;
DTR> !
DTR> ! Display the listing of domains in
DTR> ! the directory PERSONNEL.
DTR> !
DTR> SHOW DOMAINS
Domains:
      * EMPLOYEES;1

DTR> ! Display the domain definition.
DTR> !
DTR> SHOW EMPLOYEES
DOMAIN EMPLOYEES USING EMPLOYEES_REC ON EMPLOYEES.DAT;
DTR> ! Decide to make the file specification more complete.
DTR> !
DTR> EDIT EMPLOYEES
                      .        .        .
                      .        .        .
                      .        .        .
DTR> SHOW EMPLOYEES
DOMAIN EMPLOYEES USING EMPLOYEES_REC ON DBA2:[BELL]EMPLOYEES.DAT;

DTR>

As you can see from the example, you use the DEFINE DOMAIN command to begin a domain definition. The following sections provide rules and suggestions for naming the domain and specifying the record and file.

The name you choose for a domain must follow the rules that apply to any given name in the dictionary. The domain name:

When you enter a name, Datatrieve interprets lowercase letters as uppercase and a hyphen as an underscore. Datatrieve displays names in this format.

If you prefer, you can specify a full dictionary path name for a domain name. This lets you store a domain definition in a directory other than the one at which you are currently located. In Example 3.1, ''Defining a Sample Domain'', the user had the option of using the full path name CDD$TOP.PERSONNEL.EMPLOYEES in place of the partial path name EMPLOYEES to store the definition in the PERSONNEL dictionary directory. If the user had set the default dictionary setting to a dictionary directory other than PERSONNEL, a full path name, such as CDD$TOP.PERSONNEL.EMPLOYEES, or a relative path name would be necessary for storing the definition in the PERSONNEL dictionary directory. Because Datatrieve stored the EMPLOYEES definition in PERSONNEL, you know the user must have at least P (PASS_THRU) and E (EXTEND) privileges in the ACL associated with that DMU format dictionary directory.

A full path name is part of the definition, however, and has to be edited if you or someone else moves the definition later on. Most people define a domain using only the given name. Section 19.9.3, ''Using Logical Names'' describes how to abbreviate path names using logicals.

The rules that apply to the record name are the same as those for the domain name. If the record definition is (or will be) in a dictionary directory other than the one where you are storing the domain definition, you must specify a full path name for the record definition. Otherwise, you can specify the given name.

If you are specifying a full path name for a record definition in a directory that is not in your private branch of the dictionary, make sure you have P (PASS_THRU) and S (SEE) access privileges to that record definition if the definition is in a DMU format dictionary or S (SHOW) privilege if the definition is in a CDO format dictionary. You do not need these privileges to put the path name in your definition, but you need them in order to ready the domain.

The name of the data file is an OpenVMS file specification. File names are not governed by the CDD/Repository rules for naming things; they follow OpenVMS rules.

One of the key differences between a domain defined for a DMU format dictionary and one defined for a CDO format dictionary is that you can define CDO format objects with relationships. The WITH RELATIONSHIPS clause is part of the syntax of the Datatrieve DEFINE DOMAIN command. The syntax of the DEFINE DOMAIN command varies for the type of domain you are defining: RMS domain, view domain, relational domain, or remote domain. See the VSI Datatrieve Reference Manual for specific information on defining specific types of domains.

When you add the WITH RELATIONSHIPS clause to the definition of a CDO format domain, Datatrieve sets up relationships among relevant dictionary objects. The following list describes the relationships set up for each type of domain:

Datatrieve does not currently support relationships with Oracle DBMS domains.

When you define a domain using the WITH RELATIONSHIPS clause, you must be sure that you define objects in a particular order. In a relationship, one object is an owner and one is a member of the relationship. An owner object uses or depends on another object. A member object is used by another object.

When defining an object with relationships, the member object must exist before the owner object can be defined. Keep in mind, too, that all objects of a relationship must be stored in a CDO format dictionary. The following list identifies specific requirements for each type of domain:

You should also note that relationships between objects are version specific; relationships are not automatically propagated to newer versions of member objects. When you create a new version of a record, the owner domain still points to the old version of the record.

To update the relationship, you can redefine the owner (domain) definition by editing the domain definition and removing the version number following the record (or member) name. Exit the editing buffer immediately. Datatrieve updates the version numbers for you.

If a domain definition includes a partial (or relative) record path name and contains a WITH RELATIONSHIPS clause, Datatrieve treats it differently than a similar domain defined without relationships. If a domain is defined without relationships, the record that is used depends upon your default dictionary setting at the time you ready the domain.

The default dictionary directory is DISK$1:[KIRK.DTR]PERSONNEL_CDO.SALARIED when the domain in the following example is defined:

DTR>  DEFINE DOMAIN EMPLOYEES USING -
CON>  SALARIED.EMPLOYEE_REC -
CON>  ON EMP.DAT;

In this case, only the partial path name SALARIED.EMPLOYEE_REC, not the full path name, is stored in the domain definition. If the default directory is set to DISK$1:[KIRK.DTR]PERSONNEL_CDO.CONTRACT before the EMPLOYEES domain is readied, Datatrieve looks for the following record definition: DISK$1:[KIRK.DTR]PERSONNEL_CDO.CONTRACT.SALARIED.EMPLOYEE_REC. This occurs because, without relationships, Datatrieve resolves the path name when it readies the domain rather than when it defines it.

If the EMPLOYEES domain is defined using the WITH RELATIONSHIPS clause, then Datatrieve establishes the relationships with the record when the domain is defined rather than when the domain is readied. For example, if the default directory is set to DISK$1:[KIRK.DTR]PERSONNEL_CDO.SALARIED and the DEFINE DOMAIN command is used, then no matter where the default directory is set to, the domain definition for the domain DISK$1:[KIRK.DTR]SALARIED.EMPLOYEES always points to the record definition DISK$1:[KIRK.DTR]SALARIED.EMPLOYEES_REC.

DTR>  DEFINE DOMAIN EMPLOYEES -
CON>  USING SALARIED.EMPLOYEE_REC ON EMP.DAT -
CON>  WITH RELATIONSHIPS ;

DTR>

As mentioned earlier, when you define a domain with relationships, Datatrieve creates the relevant CDD/Repository objects (CDD$DATABASE, CDD$RMS_DATABASE, MCS_BINARY, and CDD$FILE_DEFINITION) for you. You have the option of creating some of these objects yourself, using the CDO utility. If you do, you can then define a Datatrieve domain based on the CDD$DATABASE object you created through CDO. To do so, you must first define and store the record in a CDO format directory, then define the CDD$RMS_DATABASE object and the CDD$DATABASE object with the CDO utility. See the VSI Datatrieve Reference Manual for more information.

Datatrieve allows you to define indexed or sequential files for your data. Sequential files require less storage, but Datatrieve must search records one by one according to their physical order in the file. This organization may be optimal in certain cases. For example, a domain’s records may contain a field for the current date, and so records are physically arranged in the order in which they are stored. If you access groups of records in chronological order, you may find this organization efficient.

05 PRODUCT_ID.
   10 VENDOR_CODE      PIC X(5).
   10 PRODUCT_CODE     PIC X(20).

In almost all cases, it is better to define an indexed file because:

You can define more than one key for an indexed file. If you do, the first key you specify is the primary key and the others are alternate keys.

You cannot change the value of a primary key field. For each alternate key, however, you can choose whether or not users can modify the value in the key field after a record is stored (CHANGE or NO CHANGE). CHANGE is a default key characteristic for alternate keys.

For both primary and alternate keys, you can choose whether or not users can store more than one record with the same value in the key field (DUP or NO DUP). NO DUP is a default key characteristic for primary keys and DUP is the default for alternate keys.

The following command explicitly specifies all key characteristics, although the characteristics specified are the Datatrieve defaults for primary and alternate keys:

DEFINE FILE FOR EMPLOYEES KEY = ID (NO CHANGE, NO DUP),
   KEY = L_NAME (CHANGE, DUP)

Records in a sequential file are positioned in the order they are written to the file. If you enter the PRINT command followed by the domain name, the first record displayed is the first one stored in the file. The last record displayed is the last one stored.

Sequential files have the advantage that report-writing procedures sometimes work more quickly when Datatrieve is processing records stored in a sequential file.

On the other hand, you cannot delete records from sequential files. You can approximate a delete operation for a sequential file by modifying all elementary field values to contain nothing but spaces or zeros. The "pseudo-erase" you must use with a sequential file, however, is time-consuming, wastes storage space, and can show up in displays and reports as a blank line. Also, record update and data retrieval operations proceed more slowly because Datatrieve must always exhaustively search the file for each record you need.

You create sequential or indexed RMS files in Datatrieve with the DEFINE FILE command. If a file is large and randomly accessed, you may want to optimize the file for better performance by using the format of the DEFINE FILE command that includes the USING fdl-file-spec clause. The USING fdl-file-spec clause specifies that the file attributes included in an existing file, defined using File Definition Language (FDL), be used to create the RMS file. Such attributes can tune your RMS file for better performance. See Section 4.4.1, ''Using EDIT/FDL to Design Your File'' for more information on using EDIT/FDL.

If you do not use the USING fdl-file-spec clause, Datatrieve uses a default for an RMS file with the following parameters:

These RMS defaults can cause data in a large indexed file to become scattered over your disk, requiring time-consuming I/O operations.

Two important considerations are bucket size and index structure. These two file attributes are related; that is, the smaller the bucket size, typically the deeper the index structure. Frequently, a major problem is that bucket size is too large, and the resulting index structure is too flat.

A bucket is the unit of transfer between storage devices and I/O buffers in memory. Bucket size is the number of blocks in a bucket. A bucket size can be from 1 to 63 blocks (each block containing 512 bytes). As a general rule, a small bucket size is optimal for randomly accessed records. (However, buckets must contain enough room for record insertion or else bucket splitting occurs. Bucket splitting fragments your data and increase I/O overhead and time.)

A flat file has an index structure with only one level. If your file contains more than a few hundred records, a single level index bucket (also called a root bucket) can be very large. A large root bucket results in slow access time for a particular data record. The following figure shows the structure of a flat file:

Flat file structure and non-optimal bucket size may be the most significant reasons for slow access time. A file with two or three levels of index structure and smaller bucket size allows you to access data much more quickly. The following figure shows a file with two index levels:

The following sections describe RMS utilities that help you design a file with more optimal bucket size and index depth. They also explain how to optimize other file attributes such as global buffers.

DEFINE FILE [FOR] domain-name USING fdl-file-spec

CREATE/FDL = fdl-file-spec file-spec.dat

The term file maintenance in this section refers to the methods you employ to optimize file storage requirements and to improve Datatrieve response time. The discussion provides an overview of the topic and is not a detailed explanation. For more information, refer to Chapter 22, "Improving Datatrieve Performance" and to the VSI OpenVMS Record Management Services Reference Manual.

You can determine the storage space assigned to your file by specifying one or more of the following arguments with the DEFINE FILE command: ALLOCATION, SUPERSEDE, MAX, KEY, and USING fdl-file-spec. When you use the DEFINE FILE command to create a data file for a domain that is stored in a CDO format dictionary and that is defined with the WITH RELATIONSHIPS clause, Datatrieve creates a special CDD/Repository entity called a CDD$FILE_DEFINITION file. This entity contains information on the characteristics of your data file. A CDD$FILE_DEFINITION definition can also be created using the CDO utility’s DEFINE RMS_DATABASE command.

When you enter a DEFINE FILE command for a domain defined using the WITH RELATIONSHIPS clause, you should be aware of the following information on how Datatrieve uses the information on the DEFINE FILE command line:

The following example of a DEFINE FILE command contains all but the MAX option from this section. The domain definition specifies the file DBA2:[BELL]FAMILY.DAT;1. Note that you specify a KEY clause last:

DTR>  DEFINE FILE FOR FAMILIES  ALLOCATION = 30,
                               SUPERSEDE,
                               KEY = FATHER (DUP)

For more information on CDO format dictionaries, see Chapter 20, "Using Datatrieve With a CDO Format Dictionary".

You might want to create new domains with data from existing ones in order to:

DTR> ! Set up READ access to the original domain. Use an alias
DTR> ! to identify the relationship between the record definition and
DTR> ! the old file. (OLD is the alias in this example.)
DTR> !
DTR>  READY EMPLOYEES AS OLD
DTR> !
DTR> ! Create an empty file with the DEFINE FILE command of your
DTR> ! choice.
DTR> !
DTR>  DEFINE FILE FOR EMPLOYEES
DTR> !
DTR> ! Set up WRITE access to your restructured domain. Use an alias
DTR> ! to identify the relationship between the record definition and
DTR> ! the new file. (NEW is the alias in this example.)
DTR> !
DTR>  READY EMPLOYEES AS NEW WRITE
DTR> !
DTR> ! Store records in the new file with a Restructure statement.
DTR> !
DTR>  NEW = OLD
DTR> !
DTR> ! End access to NEW and OLD.
DTR> !
DTR>  FINISH NEW, OLD
DTR> !
DTR> ! You can now ready the domain with its given name and can
DTR> ! access records in the new file.
DTR>

DTR> ! If NO EDIT_BACKUP is in effect during your DATATRIEVE
DTR> ! session (SHOW EDIT will tell you if it is),
DTR> ! the following command will ensure that the old version
DTR> ! of your record definition is not deleted.
DTR> !
DTR>  SET EDIT_BACKUP
DTR> !
DTR> ! Set up READ access to the original domain. Use an alias
DTR> ! to identify the relationship between the record definition and
DTR> ! the old file. (OLD is the alias in this example.)
DTR> !
DTR>  READY EMPLOYEES AS OLD
DTR> !
DTR> ! Edit the record definition. Do not change any field names.
DTR> ! If you do, DATATRIEVE will not be able to store the field
DTR> ! values. You can edit the record definition to change field
DTR> ! names after the restructure operation is completed.
DTR> !
DTR>  EDIT EMPLOYEES_REC
                        .     .     .
                        .     .     .
                        .     .     .
DTR> !
DTR> !
DTR> ! Create an empty file with the DEFINE FILE command of your
DTR> ! choice.
DTR> !
DTR>  DEFINE FILE FOR EMPLOYEES KEY = EMPLOYEE_ID
DTR> !
DTR> ! Set up WRITE access to the restructured domain. Use an alias
DTR> ! to identify the relationship between the record definition and
DTR> ! the new file. (NEW is the alias in this example.)
DTR> !
DTR>  READY EMPLOYEES AS NEW WRITE
DTR> !
DTR> ! Store records in the new file with a Restructure statement.
DTR> !
DTR>  NEW = OLD
DTR> !
DTR> ! End access to OLD and NEW.
DTR> !
DTR>  FINISH OLD, NEW
DTR> !
DTR> ! You can now ready the domain with its given name and
DTR> ! DATATRIEVE accesses records in the new file.
DTR>

PROJECTS is a sample domain supplied in the CDD$TOP.DTR$LIB.DEMO dictionary:

DTR> SET DICTIONARY CDD$TOP.DTR$LIB.DEMO
DTR> SHOW PROJECTS, PROJECT_REC
DOMAIN PROJECTS USING PROJECT_REC ON PROJECT;
RECORD PROJECT_REC
01 PROJECT_REC.
   03 PROJ_CODE     PIC 9(3)  QUERY_NAME IS CODE.
   03 PROJ_NAME     PIC X(10) QUERY_NAME IS NAME.
   03 MANAGER_NUM   PIC 9(5)  QUERY_NAME IS NUM.
;

The data file PROJECT.DAT is a sequential file and contains these records:

DTR> PRINT PROJECTS

PROJ    PROJ    MANAGER
CODE     NAME      NUM

002  GROUNDS     00006
005  BUILDING 2  00003
008  SHED        00002
018  RESEARCH    00006
037  PUB REL     00008
073  MATERIALS   00002

The following sections use this sample domain to illustrate ways to restructure data.

To create a new domain with two fields added to PROJECT_REC:

You are now ready to transfer the data from the old domain to the new one.

To transfer data from the old domain to the new one, you must first ready both domains. Ready the new domain for WRITE or EXTEND access, and ready the old one for READ access. Then use the Restructure statement to transfer the data:

DTR> READY NEW_PROJECTS WRITE
DTR> READY PROJECTS
DTR> NEW_PROJECTS = PROJECTS
DTR>

For each field name in NEW_PROJECT_REC that matches a field name in PROJECTS_REC, the Restructure statement transfers field values from each record in PROJECTS to a record in NEW_PROJECTS. For a field in the new record definition that does not match a field in the old one, Datatrieve initializes the field according to its data type and its field definition. For more information see the section on Restructure Statement in the VSI Datatrieve Reference Manual.

The data file associated with your new domain now has records in it. When you display the contents of the new domain on your terminal, you can see the two new fields and the same values contained in the PROJECTS domain:

DTR> PRINT NEW_PROJECTS

PROJ    PROJ       PROJ    MANAGER      MGR
NUM     NAME       COST      NUM        NAME

002  GROUNDS         $0.00  00006
005  BUILDING 2      $0.00  00003
008  SHED            $0.00  00002
018  RESEARCH        $0.00  00006
037  PUB REL         $0.00  00008
073  MATERIALS       $0.00  00002

DTR>

You can create the new domain from a subset of the old domain’s records. You specify the limiting conditions in the RSE of the Restructure statement. For example, you can limit a domain to the projects of two managers:

DTR> READY NEW_PROJECTS WRITE
DTR> READY PROJECTS
DTR> NEW_PROJECTS = PROJECTS WITH MANAGER_NUM EQ 2, 6
DTR> PRINT NEW_PROJECTS

PROJ    PROJ       PROJ    MANAGER      MGR
NUM     NAME       COST      NUM        NAME

002  GROUNDS         $0.00  00006
008  SHED            $0.00  00002
018  RESEARCH        $0.00  00006
073  MATERIALS       $0.00  00002

DTR>

Note that the Restructure statement relies on record definitions having the same field names. If you want to change field names, you can either edit the record definition after the restructure operation or use a STORE USING statement instead of the Restructure statement. Chapter 13, "Reporting Hierarchical Records" contains an example of using STORE USING to restructure data.

Another reason for creating a new domain is to combine the data from two or more existing domains. If you frequently use the same CROSS clause to form record streams and you cannot use a view domain because you need to store records in the domain, you can define a new domain to meet your needs. For example, when you enter data in the file of NEW_PROJECTS, you can also include the names of the managers from another domain, MANAGERS:

DTR> SHOW MANAGERS, MANAGER_REC
DOMAIN MANAGERS USING MANAGER_REC ON MGR;
RECORD MANAGER_REC USING
01 MANAGER.
   03 MANAGER_NUM         PIC 9(5).
   03 MGR_NAME            PIC X(8).
;
DTR>

Displaying the records from MANAGERS shows that values in the field MANAGER_NUM correspond to the values in the MANAGER_NUM field in the domain PROJECTS:

DTR> READY MANAGERS; PRINT MANAGERS

MANAGER      MGR
  NUM        NAME

 00002  BLOUNT
 00003  GERBLE
 00005  GORFF
 00006  PUFFNER
 00008  FEBNELL

DTR>

Using a CROSS clause in the RSE of the Restructure statement, you can match MANAGERS records with the corresponding PROJECTS records. The OVER clause allows you to match those records with matching values in the MANAGER_NUM fields. You must ready all three domains to transfer the data from PROJECTS and MANAGERS to NEW_PROJECTS:

DTR> READY NEW_PROJECTS WRITE
DTR> READY PROJECTS
DTR> READY MANAGERS
DTR> NEW_PROJECTS = PROJECTS CROSS
CON> MANAGERS OVER MANAGER_NUM
DTR>

Displaying the records in NEW_PROJECTS shows the result of the Restructure with a CROSS clause in the RSE. Notice that the value of PROJ_COST in each record is 0; the field did not exist in either of the source domains:

DTR> PRINT NEW_PROJECTS

PROJ    PROJ       PROJ    MANAGER      MGR
NUM     NAME       COST      NUM        NAME
002  GROUNDS         $0.00  00006  PUFFNER
005  BUILDING 2      $0.00  00003  GERBLE
008  SHED            $0.00  00002  BLOUNT
018  RESEARCH        $0.00  00006  PUFFNER
037  PUB REL         $0.00  00008  FEBNELL
073  MATERIALS       $0.00  00002  BLOUNT

DTR>

Sometimes you might need to merge records from two domains that store the same data using different field names. In this case, you cannot simply ready the two domains and use a Restructure statement (domain-name1 = domain-name2) to store the records from one data file into the other. You must use the FOR statement and a STORE statement that explicitly stores each elementary field. If the two domains you are merging have the same names, you have to use an alias clause when you ready them.

In the following example, the domains have different names, so the alias clause is unnecessary. All the records from EMPLOYEES_BOSTON are being stored in EMPLOYEES_ALL. In the STORE statement, the field names for EMPLOYEES_ALL are left of the equal sign (=) and the field names for EMPLOYEES_BOSTON are on the right:

DTR>  READY EMPLOYEES_BOSTON
DTR>  READY EMPLOYEES_ALL SHARED WRITE
DTR>  FOR EMPLOYEES_BOSTON
CON>  STORE EMPLOYEES_ALL USING
CON>    BEGIN
CON>      EMPLOYEE_ID = EMP_ID
CON>      LAST_NAME = NAME_LAST
CON>      FIRST_NAME = NAME_FIRST
CON>      MIDDLE_INITIAL = INIT
          .        .        .
          .        .        .
          .        .        .
CON>    END

This operation uses statements that have not been discussed so far in this book. Refer to Chapter 8, "Maintaining Data" for more information about the STORE statement.

You can use the alias clause to restructure a domain. When you use this method, you can make use of the difference between the record definition in the dictionary and the record definition controlling a readied domain in your workspace. For example, when you ready YACHTS as OLD_YACHTS, the record definition YACHT is associated with the data file YACHT.DAT.

If you then edit and redefine the format of the record definition YACHT, this change to the record is not associated with the readied domain (OLD_YACHTS); it is only associated with YACHTS the next time you ready the domain.

This method lets you make use of the difference between the record definition in the dictionary and the record definition controlling a readied domain in your

workspace. The change in the record definition does not take effect until you use the FINISH command to finish the domain and the READY command to ready it again. Simply readying the domain again does not activate the new record definition.

You can make use of this fact if you want to change a record definition or change the type of file organization of a domain’s data file. The following steps show you how to change the record definition or file type without redefining the domain. In both cases, you define a new data file and transfer the data with the Restructure statement:

You can use the alias clause of the READY command to change the organization of a data file associated with a domain. The following example replaces the indexed data file associated with YACHTS with a sequential data file:

DTR> READY YACHTS AS OLD
DTR> DEFINE FILE FOR YACHTS
DTR> READY YACHTS AS NEW WRITE
DTR> NEW = OLD
DTR> FIND NEW
[113 records found]
DTR>

Datatrieve responds faster when you use a dictionary table than when you use a domain table. That is because the table definition itself specifies all the value pairs you want to access.

The following example creates the dictionary table AREA_CODE_TABLE. This table pairs telephone area code values with corresponding state codes. The comment lines give you information about the next requirement or option in the command:

DTR> ! Start your definition with the keywords DEFINE TABLE,
DTR> ! followed by the name you want for the table.
DTR> !
DTR> DEFINE TABLE AREA_CODE_TABLE
DFN> !
DFN> ! You can include the optional QUERY_HEADER clause to specify
DFN> ! a column header for table values when you display them.
DFN> !
DFN> QUERY_HEADER IS "STATE"
DFN> !
DFN> ! You should include the optional EDIT_STRING clause to specify
DFN> ! how you want table values displayed. If you omit this clause
DFN> ! and do not include an edit string in a PRINT statement,
DFN> ! DATATRIEVE uses X(80) to display the values.
DFN> !
DFN> EDIT_STRING IS X(20)
DFN> !
DFN> ! Now enter the value pairs. The colon ( : ) is required to
DFN> ! separate values in the pair. Any spaces before and after
DFN> ! the colon are optional. You need the quotation marks to
DFN> ! preserve lowercase values or to enter values that are more
DFN> ! than one word (General Sales, for example).
DFN> !
DFN> "603" : "NH"
DFN> "617" : "MA"
DFN> "201" : "NJ"
DFN> !
DFN> ! The ELSE clause is optional. If you include it, DATATRIEVE
DFN> ! substitutes it for any values it finds in a domain and cannot
DFN> ! find in the table. If you omit it, DATATRIEVE displays an
DFN> ! error message when it cannot find the value in the table.
DFN> !
DFN> ELSE "OOPS"
DFN> !
DFN> ! You must end your definition with the keyword END_TABLE.
DFN> !
DFN> END_TABLE
DTR>

You can see how your table works with some simple PRINT statements that include a VIA clause. Note how the value associated with the ELSE clause tells you when an area code value is not listed in the table:

DTR> PRINT "603" VIA AREA_CODE_TABLE

STATE

NH

DTR> PRINT "111" VIA AREA_CODE_TABLE

STATE

OOPS

As you define a dictionary table, Datatrieve checks for syntax errors. For example, if you enter a semicolon (;) in place of the required colon (:), Datatrieve prints an error message on your terminal and aborts the DEFINE TABLE command. To correct the error, type EDIT and press the Return key. You cannot follow EDIT with the name of the table until your definition is stored. Remember that while you are using the editor, Datatrieve does not check for syntax errors. If you get an error message when you exit the editor, you can immediately type EDIT and press the Return key to try again.

Now modify your PHONES records to include some area codes, making sure that you include at least one area code that is not in the table. The last time you modified data in PHONES, you only wanted to change one record. The following example includes a FOR statement (read it as "FOR every PHONES record"), so Datatrieve lets you add all the area codes:

DTR> READY PHONES MODIFY
DTR> FOR PHONES
CON> BEGIN
CON>   PRINT
CON>   MODIFY USING AREA_CODE = *.AREA_CODE
CON>   PRINT
CON> END

        LAST             FIRST       AREA  PHONE
        NAME             NAME        CODE  NUMBER

BELL                 LISA                 555-8275
Enter AREA_CODE: 603

        LAST             FIRST       AREA  PHONE
        NAME             NAME        CODE  NUMBER

BELL                 LISA            603  555-8275
CLERC                PHYLLIS              555-9907
Enter AREA_CODE: 603
CLERC                PHYLLIS         603  555-9907
LINTE                JANE                 555-5678
Enter AREA_CODE: 603
LINTE                JANE            603  555-5678
SCHUTZ               BONNIE               555-8712
Enter AREA_CODE: 617
SCHUTZ               BONNIE          617  555-8712
WINTLOW              JOHN                 555-6789
Enter AREA_CODE: 205
WINTLOW              JOHN            205  555-6789

You can now use a VIA AREA_CODE_TABLE expression to display the state that corresponds to the area code for each record. PHONES_REC, as it appears in the PRINT statement of the following example, refers to the top-level field in the record definition rather than the record name as it is stored in the dictionary:

DTR> FOR FIRST 5 PHONES
CON> PRINT ALL PHONES_REC, AREA_CODE VIA AREA_CODE_TABLE

        LAST             FIRST       AREA  PHONE
        NAME             NAME        CODE  NUMBER  STATE

BELL                 LISA            603  555-8275 NH
CLERC                PHYLLIS         603  555-9907 NH
LINTE                JANE            603  555-5678 NH
SCHUTZ               BONNIE          617  555-8712 MA
WINTLOW              JOHN            205  555-6789 OOPS

Tables can save you a great deal of storage redundancy when they contain data that you use with more than one domain. Tables also help you validate fields that must be stored in more than one domain. In a set of domains used by the personnel department in a company, for example, the employee number would need to be stored in more than one domain.

A domain table definition contains a pair of field names; it does not contain all the value pairs you want to associate. The values for the fields are stored in the data files associated with domains. Usually several domains contain values for the shorter field and only one domain contains values for the longer field. In the sample personnel system used in this book, for example, several domains contain EMPLOYEE_ID values but only the EMPLOYEES domain contains EMPLOYEE_NAME values.

The following example defines the domain table WHO_IS_IT that associates EMPLOYEE_ID with EMPLOYEE_NAME:

DTR> ! Start your definition with DEFINE TABLE, followed by the
DTR> ! name of your table. Then enter FROM, followed by the name
DTR> ! of the domain containing both fields you want to relate.
DTR> !
DTR> DEFINE TABLE WHO_IS_IT FROM EMPLOYEES
DFN> !
DFN> ! You can include the optional QUERY_HEADER clause to specify
DFN> ! a column header for table values when you display them. If
DFN> ! the column header uses fewer character positions than the
DFN> ! associated table value, you can include spaces to position
DFN> ! the header where you want it.
DFN> !
DFN> QUERY_HEADER IS "        EMPLOYEE NAME         "
DFN> !
DFN> ! You should include the optional EDIT_STRING clause to specify
DFN> ! how you want table values displayed. If you omit this clause
DFN> ! and do not include an edit string in a PRINT statement,
DFN> ! DATATRIEVE uses X(80) to display the values.
DFN> !
DFN> EDIT_STRING IS X(36)
DFN> !
DFN> ! Now enter the field names. The colon ( : ) is required to
DFN> ! separate them. Any spaces before and after the colon are
DFN> ! optional. The keyword USING is also optional.
DFN> !
DFN> USING EMPLOYEE_ID : EMPLOYEE_NAME
DFN> !
DFN> ! The ELSE clause is optional. If you include it, DATATRIEVE
DFN> ! substitutes it for any values it finds in one domain and cannot
DFN> ! find in the other (table) domain. If you omit it, DATATRIEVE
DFN> ! displays an error message when it cannot find the value in the
DFN> ! domain on which the table is based. Use quotation marks to pre-
DFN> ! serve lowercase letters or if the ELSE value contains spaces.
DFN> !
DFN> ELSE "ID not in EMPLOYEES."
DFN> !
DFN> ! You must end your definition with the keyword END_TABLE.
DFN> !
DFN> END_TABLE
DTR>

Tables are useful in record definitions both for validation and for saving storage space. The record for PERSONNEL can be improved by using tables. The current definition of PERSONNEL_REC contains the following field definition:

05 EMPLOYEE_STATUS PIC IS X(11)
                   QUERY_NAME IS STATUS
                   QUERY_HEADER IS "STATUS"
                   VALID IF STATUS EQ "TRAINEE","EXPERIENCED".

EMPLOYEE_STATUS is an 11-byte field that takes only two values: TRAINEE or EXPERIENCED. Rather than storing the 11 bytes for each record, you could use a table to translate the value for a 1-byte status code. This technique saves 10 bytes of storage per record and reduces time for data entry.

The following example shows the definition for the dictionary table STATUS_TABLE:

DTR> DEFINE TABLE STATUS_TABLE
DFN>    E      :      EXPERIENCED
DFN>    T      :      TRAINEE
DFN> END_TABLE

You can now edit PERSONNEL_REC, deleting the EMPLOYEE_STATUS field and adding two new fields that reference the table. EMP_STATUS_CODE validates entries for the status code by checking the table. EMP_STATUS, a virtual field, translates these code entries to either "EXPERIENCED" or "TRAINEE":

05 EMP_STATUS_CODE  PIC X
                    QUERY_NAME IS S_CODE
                    VALID IF EMP_STATUS_CODE IN STATUS_TABLE.

05 EMP_STATUS       COMPUTED BY
                    EMP_STATUS_CODE VIA STATUS_TABLE.

Because the new definition defines a record with 10 fewer bytes, you need to define a new file for PERSONNEL. The following procedure illustrates how to define a new file for PERSONNEL and restructure the data to match the new record definition with the STORE USING statement:

DTR> SHOW RESTRUCTURE_PERSONNEL
PROCEDURE RESTRUCTURE_PERSONNEL
READY PERSONNEL AS OLD
DEFINE FILE FOR PERSONNEL KEY = ID
READY PERSONNEL AS NEW WRITE
FOR O IN OLD STORE N IN NEW USING
        BEGIN
                N.ID = O.ID
                CHOICE
                    O.EMPLOYEE_STATUS = "EXPERIENCED" THEN
                    N.EMP_STATUS_CODE = "E"
                    O.STATUS = "TRAINEE" THEN
                    N.EMP_STATUS_CODE = "T"
                END_CHOICE
                N.FIRST_NAME = O.FIRST_NAME
                N.LAST_NAME = O.LAST_NAME
                N.DEPT = O.DEPT
                N.START_DATE = O.START_DATE
                N.SALARY = O.SALARY
                N.SUP_ID = O.SUP_ID
        END
END_PROCEDURE

DTR>

Table definitions, like all dictionary objects created by Datatrieve, have associated ACLs that determine who can use them. For more information on ACLs, see the appendix on Access Privileges Tables in the VSI Datatrieve Reference Manual.

DTR> READY JOB_HISTORY
DTR> ! Form a collection of all records in JOB_HISTORY whose
DTR> ! department codes are not in the table.
DTR> !
DTR> FIND ALL JOB_HISTORY WITH DEPARTMENT_CODE NOT IN
CON> DEPARTMENTS_TABLE
[705 records found]
DTR> !
DTR> ! Print values for two fields in the collection’s records
DTR> ! and append a related field to each record by accessing the
DTR> ! table. Note that the value in the ELSE clause of the table
DTR> ! definition appears.
DTR> !
DTR> PRINT ALL EMPLOYEE_ID, DEPARTMENT_CODE,
CON> DEPARTMENT_CODE VIA DEPARTMENTS_TABLE

EMPLOYEE DEPARTMENT      DEPARTMENT
   ID       CODE            NAME

 00164      MBMN    Invalid Dept.
 00164      MCBM    Invalid Dept.
 00165      ELGS    Invalid Dept.
 00165      PHRN    Invalid Dept.
 00166      MB Ctrl/C
^C
Execution terminated by operator.

DTR> !
DTR> ! Assume these values are valid department codes that you
DTR> ! need to add to the table. By reducing the current collection
DTR> ! to unique department code values and then printing the
DTR> ! reduced collection, you get a list of what needs to be
DTR> ! added to DEPARTMENTS_TABLE.
DTR> !
DTR> REDUCE TO DEPARTMENT_CODE
DTR> PRINT ALL

DEPARTMENT
   CODE

   ELEL
   ELGS
   ELMC
   MBMF
   MBMN
   .
   .
   .
DTR>

DTR> SHOW JOB_HISTORY_REC
RECORD JOB_HISTORY_REC USING
01  JOB_HISTORY_REC.
        05  DEPARTMENT_CODE    PIC X(4)
                               VALID IF DEPARTMENT_CODE IN
                               DEPARTMENTS_TABLE.
        05  EMPLOYEE_ID        PIC X(5)
                               VALID IF EMPLOYEE_ID IN
                               WHO_IS_IT.
        05  JOB_CODE           PIC X(4).
        05  JOB_START          USAGE DATE.
        05  JOB_END            USAGE DATE.
        05  REVIEW_DATE        USAGE DATE.
        05  SUPERVISOR_ID      PIC X(5).
;
DTR> ! When users store or modify records in the
DTR> ! JOB_HISTORY domain, DATATRIEVE checks a
DTR> ! value entered for DEPARTMENT_CODE against
DTR> ! codes in DEPARTMENTS_TABLE and a value entered
DTR> ! for EMPLOYEE_ID against codes in WHO_IS_IT.
DTR> !
DTR> READY JOB_HISTORY WRITE
DTR> STORE JOB_HISTORY
Enter DEPARTMENT_CODE: XXXX
Validation error for field DEPARTMENT_CODE.
Re-enter DEPARTMENT_CODE: SALE
Enter EMPLOYEE_ID: 00000
Validation error for field EMPLOYEE_ID.
Re-enter EMPLOYEE_ID: 00168
Enter JOB_CODE:
                   .         .         .
                   .         .         .
                   .         .         .
DTR>

To decide which type of table to use, keep the following guidelines in mind:

When you ready a domain, Datatrieve loads the record definition associated with the domain into your workspace and opens the associated data file. In addition to the domain name, a READY command can include the following:

You have to specify an alias only if two domains with the same given name will be ready at the same time. This situation can occur when you are accessing domains stored in different dictionary directories and when you are restructuring your database. In the command READY EMPLOYEES AS NEW WRITE, for example, NEW is an alias. When you ready a domain under an alias, you must use the alias rather than the domain’s given name in any subsequent statements or commands during that session. Datatrieve does not recognize the readied domain if you use the given name.

PROTECTED is the access option that applies if you do not specify one. PROTECTED means that if other users ready the domain while you are using it, they can use it only to retrieve and display data. They cannot modify, erase, or add records.

READ is the access mode that applies if you do not specify one. READ means that you can use the domain only to retrieve and display data. You cannot store, erase, or modify records without readying the domain again to specify a new access mode.

In addition to the domain definition privileges, you also need privileges to an associated record definition.

For more information on access modes and options, see the VSI Datatrieve Reference Manual.

DTR> READY YACHTS_CDO
"YACHTS_CDO" uses an entity which has new versions,
triggered by RECORD entity "DISK$1:[KIRK.DTR]SAMPLE.YACHT_CDO_REC;2".
[Record is 41 bytes long.]
DTR>

DTR> READY CDO_DB

Use the FINISH command to end your control over one or more domains. If you specify more than one domain name in the FINISH command, enter commas to separate the domain names. If you enter the keyword FINISH by itself, or if you enter FINISH ALL, you end your control over all the domains you have readied.

Finishing domains is especially important if you have readied any domains with the PROTECTED or EXCLUSIVE access option and other users access those domains. The PROTECTED option keeps other users from updating the data file. The EXCLUSIVE access option locks out other users entirely. In addition, if you ready a domain with the EXCLUSIVE access option and that domain is the base for a domain table, you must finish the domain before you can use the domain table.

You can define OpenVMS logical names to control the way Datatrieve handles the interpretation of dates and currency symbols. You can control the format Datatrieve uses to interpret the input of dates by defining the logical name DTR$DATE_INPUT. This logical name affects only the interpretation of the input of dates and has nothing to do with the edit strings used for the output of dates.

You define DTR$DATE_INPUT with a three-character string containing one D for day, one M for month, and one Y for year. Enclose the three-character string in quotation marks.

The command DEFINE DTR$DATE_INPUT "MDY" defines a date input of 03/12/09 as March 12, 1909. MDY is the default interpretation Datatrieve uses for input of dates if you do not define DTR$DATE_INPUT.

The following table shows the different combinations you can use:

The format you choose also controls the format Datatrieve uses to convert six-digit numeric strings (such as 810210) to dates.

You can define DTR$DATE_INPUT at the DCL command level, or you can put the appropriate DEFINE command in your login command file. The following table shows the three logical names you can define to control the currency defaults of the OpenVMS operating system.

The following examples demonstrate the effects of redefining these logical names. In the first example, you redefine the default values:

$ DEFINE SYS$CURRENCY "# "
$ DEFINE SYS$DIGIT_SEP "."
$ DEFINE SYS$RADIX_POINT ","

In the next example, you can see the results of the changed definitions:

DTR> DECLARE NUM PIC 9(6)V99.
DTR> NUM = 12345.67
DTR> PRINT NUM USING $$$,$$$.99

   NUM

# 12.345,67

A record selection expression has the following format:

The format for rse-source is as follows:

The domain-name includes Oracle DBMS domains, relational domains, RMS domains, view domains, and network domains. Note that the MEMBER, OWNER, and WITHIN set-name syntax is used only with an Oracle DBMS domain name or Oracle DBMS record name.

You can use RSEs in all of the following Datatrieve statements:

In addition, you can use RSEs to specify subsets of records when you define view domains. See Chapter 12, "Accessing Data the Expert Way: Using RSEs and View Domains" for more information on RSEs in view domain definitions.

The format diagram shows that the RSE contains one required element and seven optional elements. The following sections describe each element.

The name of the record source is the only required element in an RSE. You must specify one of the five possible sources of the record stream:

This element tells Datatrieve which RMS, relational database, or Oracle DBMS domain, collection, list, relation, or Oracle DBMS record contains the records to search when forming a record stream.

You can use record selection expressions to access remote data, but the RSEs cannot contain expressions that Datatrieve must evaluate on the remote node.

DTR> READY YACHTS
DTR> PRINT YACHTS

                               LENGTH
                                OVER
MANUFACTURER    MODEL    RIG    ALL    WEIGHT BEAM  PRICE

 ALBERG       37 MK II  KETCH   37     20,000  12  $35,000
 ALBIN        79        SLOOP   26      4,200  10  $17,900
        .                .                .
        .                .                .
        .                .                .

DTR>

DTR> READY YACHTS
DTR> FIND YACHTS
[113 records found]
DTR> PRINT CURRENT

                               LENGTH
                                OVER
MANUFACTURER    MODEL    RIG    ALL    WEIGHT BEAM  PRICE

 ALBERG       37 MK II  KETCH   37     20,000  12  $35,000
 ALBIN        79        SLOOP   26      4,200  10  $17,900
        .                .                .
        .                .                .
        .                .                .

DTR>

DTR> FIND BIG_ONES IN YACHTS WITH LOA > 40
[8 records found]
DTR> PRINT BIG_ONES WITH PRICE NE 0

                               LENGTH
                                OVER
MANUFACTURER    MODEL     RIG    ALL   WEIGHT BEAM  PRICE

 CHALLENGER   41         KETCH   41   26,700   13  $51,228
 COLUMBIA     41         SLOOP   41   20,700   11  $48,490
 GULFSTAR     41         KETCH   41   22,000   12  $41,350
 ISLANDER     FREEPORT   KETCH   41   22,000   13  $54,970
 OLYMPIC      ADVENTURE  KETCH   42   24,250   13  $80,500

DTR>

DTR> READY FAMILIES
DTR> FIND FAMILIES
[14 records found]
DTR> SELECT
DTR> PRINT AGE OF FIRST 1 KIDS

AGE

 7

DTR>

DTR> FOR FAMILIES WITH NUMBER_KIDS = 2
CON>    PRINT KID_NAME, AGE OF KIDS WITH AGE GT 20

   KID
   NAME    AGE

ANN        29
JEAN       26
MARTHA     30
TOM        27

DTR>

DTR> READY FAMILIES WRITE
DTR> MODIFY EACH_KID OF FIRST 1 KIDS
"KIDS" is undefined or used out of context
DTR>

DTR> READY FAMILIES WRITE
DTR> FOR FAMILIES MODIFY EACH_KID OF FIRST 1 KIDS
Enter KID_NAME:

DTR> ERASE ALL OF KIDS
"KIDS" is undefined or used out of context
DTR>

DTR> FOR FIRST 2 FAMILIES
[Looking for statement]
CON>      MODIFY EACH_KID OF KIDS
Enter KID_NAME: Ctrl/Z
Execution terminated by operator
DTR> FIND FIRST 2 FAMILIES
[2 records found]
DTR> SELECT
DTR> MODIFY EACH_KID OF KIDS
Enter KID_NAME: Ctrl/Z
Execution terminated by operator
DTR>

DTR> READY PARTS_DB

DTR> FIND DIVISIONS
DTR> SELECT 3
DTR> PRINT EMPLOYEES MEMBER CONSISTS_OF

                                        Phone
Ident  Last Name----------- First Name  Number   Loc

 99998 PAYNE                RONALD      8902345 23456

DTR>

DTR> FIND EMPLOYEES CROSS PART_S MEMBER RESPONSIBLE_FOR
DTR> PRINT EMP_LAST_NAME, PART_DESC OF CURRENT

[66 records found]
DTR>

DTR> DEFINE DOMAIN WHOLE_DIVISION
DEF>  OF DIVISIONS, EMPLOYEES USING
DEF> 01 DIV OCCURS FOR DIVISIONS.
DEF>  02 DIV_NAME FROM DIVISIONS.
DEF>  02 WORKERS OCCURS FOR EMPLOYEES MEMBER CONSISTS_OF.
DEF>       04 EMP_ID FROM EMPLOYEES.
DEF>       04 EMP_NAME FROM EMPLOYEES.
DEF> ;
DTR>

DTR> FIND A IN COMPONENTS CROSS
CON> PART_S OWNER PART_USED_ON CROSS
CON> PART_S OWNER OF A.PART_USES
[119 records found]
DTR>

ALL print-list OF rse

DTR> FOR DIVISIONS
CON> PRINT DIV_NAME, ALL EMP_LAST_NAME OF
CON> EMPLOYEES OWNER OF MANAGES

Division Name------- Last Name-----------

LA34 DEVELOPMENT     ZAHAN
SOFTWARE             SCHATZEL
RM05 DEVELOPMENT     ZOPF
ENG BUILD & TEST     THOMPSON
VT100 DEVELOPMENT    DALE
           .
           .
           .

DTR>

DTR> PRINT PART_S CROSS SUP IN SUPPLIES WITHIN
CON> PART_INFO CROSS
CON> VENDORS WITHIN SUP.VENDOR_SUPPLY

   Part                                               Unit
  Number   ------------Part Description---------- St Price

CE-3556-78 VT100 NON REFLECTIVE SCREEN            G   $26
         $20  NO  G  MEMO 14      02321332
U.S. SEALS                          R.R. BINGHAM
132 MAIN ST.
MOLINE, ILL
                816,884,5398
AS-1110-85 1N970B DIODE                           G   $20
         $17  NO  G   REPR 2      12345678
HIGH ENERGY CORP                    GIADONE ALEX
500 DOVER RD.
MAYNARD, MA
                617,555,6666

       .
       .
       .

DTR>

DTR> FOR DIVISIONS
CON> FOR EMPLOYEES WITHIN CONSISTS_OF
CON> PRINT DIV_NAME, EMP_LAST_NAME

Division Name------- Last Name-----------

LA34 DEVELOPMENT     FRATUS
SOFTWARE             HUTCHINGS
SOFTWARE             IACOBONE
SOFTWARE             PASCAL
RM05 DEVELOPMENT     PAYNE
ENG BUILD & TEST     FRASER
ENG BUILD & TEST     HORYMSKI
ENG BUILD & TEST     HUMPHRY
ENG BUILD & TEST     MASE
ENG BUILD & TEST     PARVIA

       .
       .
       .

DTR>

If a domain does not contain many records, you may want to display all of the records. In that case, you use the simplest form of an RSE, the domain name by itself. Since you want Datatrieve to print all of the records in that domain, you do not use any clauses of the RSE to select specific records. For example:

DTR> READY PERSONNEL
DTR> PRINT PERSONNEL

                    FIRST       LAST            START             SUP
 ID     STATUS      NAME        NAME    DEPT    DATE     SALARY   ID

00012 EXPERIENCED CHARLOTTE  SPIVA      TOP  12-Sep-1972 $75,892 00012
00891 EXPERIENCED FRED       HOWL       F11   9-Apr-1976 $59,594 00012
02943 EXPERIENCED CASS       TERRY      D98   2-Jan-1980 $29,908 39485
12643 TRAINEE     JEFF       TASHKENT   C82   4-Apr-1981 $32,918 87465
32432 TRAINEE     THOMAS     SCHWEIK    F11   7-Nov-1981 $26,723 00891
34456 TRAINEE     HANK       MORRISON   T32   1-Mar-1982 $30,000 87289
38462 EXPERIENCED BILL       SWAY       T32   5-May-1980 $54,000 00012
38465 EXPERIENCED JOANNE     FREIBURG   E46  20-Feb-1980 $23,908 48475
39485 EXPERIENCED DEE        TERRICK    D98   2-May-1977 $55,829 00012
48475 EXPERIENCED GAIL       CASSIDY    E46   2-May-1978 $55,407 00012
48573 TRAINEE     SY         KELLER     T32   2-Aug-1981 $31,546 87289
49001 EXPERIENCED DAN        ROBERTS    C82   7-Jul-1979 $41,395 87465
49843 TRAINEE     BART       HAMMER     D98   4-Aug-1981 $26,392 39485
78923 EXPERIENCED LYDIA      HARRISON   F11  19-Jun-1979 $40,747 00891
83764 EXPERIENCED JIM        MEADER     T32   4-Apr-1980 $41,029 87289
84375 EXPERIENCED MARY       NALEVO     D98   3-Jan-1976 $56,847 39485
87289 EXPERIENCED LOUISE     DEPALMA    G20  28-Feb-1979 $57,598 00012
87465 EXPERIENCED ANTHONY    IACOBONE   C82   2-Jan-1973 $58,462 00012
87701 TRAINEE     NATHANIEL  CHONTZ     F11  28-Jan-1982 $24,502 00891
88001 EXPERIENCED DAVID      LITELLA    G20  11-Nov-1980 $34,933 87289
90342 EXPERIENCED BRUNO      DONCHIKOV  C82   9-Aug-1978 $35,952 87465
91023 TRAINEE     STAN       WITTGEN    G20  23-Dec-1981 $25,023 87289
99029 EXPERIENCED RANDY      PODERESIAN C82  24-May-1979 $33,738 87465

DTR>

After you ready the domain, the PRINT PERSONNEL statement displays all the records in the PERSONNEL domain. The source for the RSE is PERSONNEL, the name of the domain.

To indicate clearly that you want the record stream to include all the records, you can include the keyword ALL before the source of the RSE. Because the ALL is optional, PRINT ALL PERSONNEL is equivalent to PRINT PERSONNEL.

There are several ways to limit the number of records in the record stream. One way is to restrict the record stream to the first n records in the domain or collection. This type of RSE is useful when you know the order of records and the exact number of records you wish to access.

To specify the number of records in the record stream, type FIRST followed by a number before typing the source for the RSE. For example:

DTR> PRINT FIRST 5 PERSONNEL

                    FIRST       LAST            START             SUP
 ID     STATUS      NAME        NAME    DEPT    DATE     SALARY   ID

00012 EXPERIENCED CHARLOTTE  SPIVA      TOP  12-Sep-1972 $75,892 00012
00891 EXPERIENCED FRED       HOWL       F11   9-Apr-1976 $59,594 00012
02943 EXPERIENCED CASS       TERRY      D98   2-Jan-1980 $29,908 39485
12643 TRAINEE     JEFF       TASHKENT   C82   4-Apr-1981 $32,918 87465
32432 TRAINEE     THOMAS     SCHWEIK    F11   7-Nov-1981 $26,723 00891

DTR>

In this case, the RSE is FIRST 5 PERSONNEL. Datatrieve displays the first five records in PERSONNEL, according to their order in the data file. An RSE can have the form FIRST n domain-name or FIRST n collection-name. If n is larger than the number of records in the domain or collection, Datatrieve displays all the records in that source.

RSEs let you work with records from different sources. The CROSS clause of the RSE lets you form record streams by combining data from two or more sources of records. It forms temporary relationships between records stored in different data files based on the relationship between field values in the different files. Joining records with the CROSS clause allows you to treat the data as though it derived from one data file.

With the CROSS clause, you can perform the following tasks:

7.5.1. Using CROSS to Combine Two Domains

Suppose you want to find the prices of individual boats in the YACHTS domain that belong to boat owners stored in the OWNERS domain. You want to combine OWNERS records with YACHTS records that have the same MODEL and MANUFACTURER. The RSE that forms this temporary combination of records is on the second input line of the following PRINT statement. The group field TYPE, which includes both MANUFACTURER and MODEL, is the primary key for the YACHT.DAT file. It is defined as NO DUP; as a result, no two boats can have the same value for TYPE:

DTR> PRINT NAME, TYPE, PRICE OF
CON> YACHTS CROSS OWNERS OVER TYPE

   NAME    MANUFACTURER   MODEL     PRICE

STEVE       ALBIN       VEGA       $18,600
HUGH        ALBIN       VEGA       $18,600
JIM         C&C         CORVETTE
ANN         C&C         CORVETTE
JIM         ISLANDER    BAHAMA      $6,500
ANN         ISLANDER    BAHAMA      $6,500
STEVE       ISLANDER    BAHAMA      $6,500
HARVEY      ISLANDER    BAHAMA      $6,500
TOM         PEARSON     10M
DICK        PEARSON     26
JOHN        RHODES      SWIFTSURE

DTR>

The OVER TYPE phrase takes the place of WITH OWNERS.TYPE = YACHTS.TYPE. This RSE forms a record stream of 11 records.

The following figure illustrates the way Datatrieve joins records from two domains. Datatrieve reads each record from the first source trying to find matches on the TYPE field. When Datatrieve finds a match, it joins the record from OWNERS with the record from YACHTS:

You can use CROSS to join two domains on the same remote node. However, you cannot join domains on different nodes.

DTR> READY YACHTS
DTR> FIND AMERICAN_YACHTS IN YACHTS WITH
CON> BUILDER = "AMERICAN"
[2 records found]
DTR> FIND ALBIN_YACHTS IN YACHTS WITH BUILDER = "ALBIN"
[3 records found]
DTR> LIST AMERICAN_YACHTS CROSS ALBIN_YACHTS OVER RIG

MANUFACTURER    : ALBIN
MODEL           : 79
RIG             : SLOOP
LENGTH_OVER_ALL : 26
DISPLACEMENT    : 4,200
BEAM            : 10
PRICE           : $17,900
MANUFACTURER    : ALBIN
MODEL           : 79
RIG             : SLOOP
LENGTH_OVER_ALL : 26
DISPLACEMENT    : 4,200
BEAM            : 10
PRICE           : $17,900
            .
            .
            .

DTR> READY YACHTS, YACHTS AS EXTRA
DTR> FIND AMERICAN_YACHTS IN YACHTS WITH
CON> BUILDER = "AMERICAN"
[2 records found]
DTR> FIND ALBIN_YACHTS IN EXTRA WITH BUILDER = "ALBIN"
[3 records found]
DTR> LIST AMERICAN_YACHTS CROSS ALBIN_YACHTS OVER RIG

MANUFACTURER    : AMERICAN
MODEL           : 26
RIG             : SLOOP
LENGTH_OVER_ALL : 26
DISPLACEMENT    : 4,000
BEAM            : 08
PRICE           : $9,895
MANUFACTURER    : ALBIN
MODEL           : 79
RIG             : SLOOP
LENGTH_OVER_ALL : 26
DISPLACEMENT    : 4,200
BEAM            : 10
PRICE           : $17,900
            .
            .
            .

DTR> READY YACHTS, YACHTS AS EXTRA
DTR> SHOW READY
Ready sources:
   EXTRA:  Domain, RMS sequential, protected read
           <CDD$TOP.DTR$LIB.DEMO.YACHTS;1>
   YACHTS:  Domain, RMS sequential, protected read
           <CDD$TOP.DTR$LIB.DEMO.YACHTS;1>
No loaded tables.

DTR> PRINT BUILDER, A.RIG, RIG OF A IN YACHTS CROSS
[Looking for name of domain, collection, or list]
CON> YACHTS OVER BUILDER WITH A.RIG GT RIG

MANUFACTURER  RIG    RIG

 AMERICAN    SLOOP  MS
 CHALLENGER  SLOOP  KETCH
 CHALLENGER  SLOOP  KETCH
 GRAMPIAN    SLOOP  KETCH
          .
          .
          .
 PEARSON     SLOOP  KETCH

DTR>

Often you are interested in grouping similar records together, regardless of their physical position in the data file. You can restrict the record stream to those records that satisfy a specific condition by using the WITH clause of the RSE. Different types of WITH clauses reflect different types of relationships among the values of the same field for different records. Records can be grouped if they are related by the following conditions:

DTR> PRINT YACHTS WITH RIG = "MS"

                               LENGTH
                                OVER
MANUFACTURER    MODEL    RIG    ALL    WEIGHT BEAM  PRICE

 AMERICAN     26-MS     MS      26      5,500  08  $18,895
 EASTWARD     HO        MS      24      7,000  09  $15,900
 FJORD MS     33        MS      33     14,000  11
 LINDSEY      39        MS      39     14,500  12  $35,900
 ROGGER FD    M/S       MS      35     17,600  11

DTR>

DTR> PRINT YACHTS WITH BUILDER = "ALBIN", "ALBERG"

                               LENGTH
                                OVER
MANUFACTURER    MODEL    RIG    ALL    WEIGHT BEAM  PRICE

 ALBIN        79        SLOOP   26      4,200  10  $17,900
 ALBIN        BALLAD    SLOOP   30      7,276  10  $27,500
 ALBIN        VEGA      SLOOP   27      5,070  08  $18,600
 ALBERG       37 MK II  KETCH   37     20,000  12  $36,951

DTR> PRINT YACHTS WITH RIG NE "SLOOP", "KETCH"

                               LENGTH
                                OVER
MANUFACTURER    MODEL    RIG    ALL    WEIGHT BEAM  PRICE

 AMERICAN     26-MS     MS      26      5,500  08  $18,895
 EASTWARD     HO        MS      24      7,000  09  $15,900
 FJORD MS     33        MS      33     14,000  11
 LINDSEY      39        MS      39     14,500  12  $35,900
 ROGGER FD    M/S       MS      35     17,600  11

DTR>

DTR> FIND YACHTS WITH BUILDER = "Albin"
[0 records found]
DTR> FIND YACHTS WITH BUILDER NOT_EQUAL "Albin"
[113 records found]

DTR> FIND YACHTS WITH BUILDER CONT "Albin"
[3 records found]
DTR> FIND YACHTS WITH BUILDER CONT "bin"
[3 records found]

DTR> PRINT YACHTS WITH BUILDER STARTING WITH "AL"

                               LENGTH
                                OVER
MANUFACTURER    MODEL    RIG    ALL    WEIGHT BEAM  PRICE

 ALBERG       37 MK II  KETCH   37     20,000  12  $36,951
 ALBIN        79        SLOOP   26      4,200  10  $17,900
 ALBIN        BALLAD    SLOOP   30      7,276  10  $27,500
 ALBIN        VEGA      SLOOP   27      5,070  08  $18,600

DTR>

DTR> PRINT PERSONNEL WITH SALARY GREATER_EQUAL 54000

                    FIRST       LAST            START             SUP
 ID     STATUS      NAME        NAME    DEPT    DATE     SALARY   ID

00012 EXPERIENCED CHARLOTTE  SPIVA      TOP  12-Sep-1972 $75,892 00012
00891 EXPERIENCED FRED       HOWL       F11   9-Apr-1976 $59,594 00012
38462 EXPERIENCED BILL       SWAY       T32   5-May-1980 $54,000 00012
39485 EXPERIENCED DEE        TERRICK    D98   2-May-1977 $55,829 00012
48475 EXPERIENCED GAIL       CASSIDY    E46   2-May-1978 $55,407 00012
84375 EXPERIENCED MARY       NALEVO     D98   3-Jan-1976 $56,847 39485
87289 EXPERIENCED LOUISE     DEPALMA    G20  28-Feb-1979 $57,598 00012
87465 EXPERIENCED ANTHONY    IACOBONE   C82   2-Jan-1973 $58,462 00012

DTR> PRINT PERSONNEL WITH SALARY BETWEEN 30000 AND 54000

                    FIRST       LAST            START             SUP
 ID     STATUS      NAME        NAME    DEPT    DATE     SALARY   ID

12643 TRAINEE     JEFF       TASHKENT   C82   4-Apr-1981 $32,918 87465
34456 TRAINEE     HANK       MORRISON   T32   1-Mar-1982 $30,000 87289
38462 EXPERIENCED BILL       SWAY       T32   5-May-1980 $54,000 00012
48573 TRAINEE     SY         KELLER     T32   2-Aug-1981 $31,546 87289
49001 EXPERIENCED DAN        ROBERTS    C82   7-Jul-1979 $41,395 87465
78923 EXPERIENCED LYDIA      HARRISON   F11  19-Jun-1979 $40,747 00891
83764 EXPERIENCED JIM        MEADER     T32   4-Apr-1980 $41,029 87289
88001 EXPERIENCED DAVID      LITELLA    G20  11-Nov-1980 $34,933 87289
90342 EXPERIENCED BRUNO      DONCHIKOV  C82   9-Aug-1978 $35,952 87465
99029 EXPERIENCED RAINED     PODERESIAN C82  24-May-1979 $33,738 87465

DTR> PRINT PERSONNEL WITH START_DATE
CON> AFTER "1-Jan-1981"

                    FIRST       LAST            START             SUP
 ID     STATUS      NAME        NAME    DEPT    DATE     SALARY   ID

12643 TRAINEE     JEFF       TASHKENT   C82   4-Apr-1981 $32,918 87465
32432 TRAINEE     THOMAS     SCHWEIK    F11   7-Nov-1981 $26,723 00891
34456 TRAINEE     HANK       MORRISON   T32   1-Mar-1982 $30,000 87289
48573 TRAINEE     SY         KELLER     T32   2-Aug-1981 $31,546 87289
49843 TRAINEE     BART       HAMMER     D98   4-Aug-1981 $26,392 39485
87701 TRAINEE     NATHANIEL  CHONTZ     F11  28-Jan-1982 $24,502 00891
91023 TRAINEE     STAN       WITTGEN    G20  23-Dec-1981 $25,023 87289

DTR>

DTR> FIND PERSONNEL WITH SUP_ID MISSING
[0 records found]

DTR> FIND PERSONNEL WITH SUP_ID NOT MISSING
[23 records found]
DTR>

PERSONNEL WITH SUP_ID IN SUP_TABLE

DTR> PRINT PERSONNEL WITH START_DATE BEFORE
CON> "1-Jan-1979" AND SALARY LT 36000

                    FIRST       LAST            START             SUP
 ID     STATUS      NAME        NAME    DEPT    DATE     SALARY   ID

90342 EXPERIENCED BRUNO      DONCHIKOV  C82   9-Aug-1978 $35,952 87465

DTR> PRINT PERSONNEL WITH DEPT = "TOP" OR SALARY > 54000

                    FIRST       LAST            START             SUP
 ID     STATUS      NAME        NAME    DEPT    DATE     SALARY   ID

00012 EXPERIENCED CHARLOTTE  SPIVA      TOP  12-Sep-1972 $75,892 00012
00891 EXPERIENCED FRED       HOWL       F11   9-Apr-1976 $59,594 00012
39485 EXPERIENCED DEE        TERRICK    D98   2-May-1977 $55,829 00012
48475 EXPERIENCED GAIL       CASSIDY    E46   2-May-1978 $55,407 00012
84375 EXPERIENCED MARY       NALEVO     D98   3-Jan-1976 $56,847 39485
87289 EXPERIENCED LOUISE     DEPALMA    G20  28-Feb-1979 $57,598 00012
87465 EXPERIENCED ANTHONY    IACOBONE   C82   2-Jan-1973 $58,462 00012

DTR>

DTR> PRINT PERSONNEL WITH SALARY > 54000 BUT DEPT = "TOP"

                    FIRST       LAST            START             SUP
 ID     STATUS      NAME        NAME    DEPT    DATE     SALARY   ID

00012 EXPERIENCED CHARLOTTE  SPIVA      TOP  12-Sep-1972 $75,892 00012

DTR>

Frequently, a record stream contains several records that have the same values for a specific field. To find the unique field values (that is, to eliminate duplicate field values from the record stream), use the REDUCED TO clause of the RSE.

Up to this point, the RSE clauses have let you limit the number of records in the record stream. The REDUCED TO clause of the RSE lets you limit the fields within each record in the record stream. For example, if you want to know the names of all of the departments in the PERSONNEL domain, you can use the following query:

DTR> FIND PERSONNEL REDUCED TO DEPT
[7 records found]
DTR> PRINT CURRENT

DEPT

C82
D98
E46
F11
G20
T32
TOP

To process the RSE, Datatrieve searches the values for DEPT and finds seven unique values. Datatrieve then generates a collection of seven records with values for the DEPT field only.

Sometimes you want to know all the unique combinations of values for several fields in the record. To find the combinations of values for DEPT and STATUS, use the RSE "PERSONNEL REDUCED TO DEPT, STATUS":

DTR> FIND PERSONNEL REDUCED TO DEPT, STATUS
[12 records found]
DTR> PRINT CURRENT

DEPT   STATUS

C82  EXPERIENCED
C82  TRAINEE
D98  EXPERIENCED
D98  TRAINEE
E46  EXPERIENCED
F11  EXPERIENCED
F11  TRAINEE
G20  EXPERIENCED
G20  TRAINEE
T32  EXPERIENCED
T32  TRAINEE
TOP  EXPERIENCED

The REDUCED TO clause is a powerful tool for forming relational queries. For example, the following query uses two RSEs to display the names of all the supervisors and the departments they manage:

DTR> FOR A IN PERSONNEL REDUCED TO SUP_ID
[Looking for statement]
CON> PRINT DEPT, NAME, ID OF PERSONNEL WITH ID = A.SUP_ID

       FIRST       LAST
DEPT   NAME        NAME    ID

TOP  CHARLOTTE  SPIVA     00012
F11  FRED       HOWL      00891
D98  DEE        TERRICK   39485
E46  GAIL       CASSIDY   48475
G20  LOUISE     DEPALMA   87289
C82  ANTHONY    IACOBONE  87465

DTR>

This query finds every employee who is a supervisor, that is, whose ID equals one of the values specified by the REDUCED TO clause. The RSE "A IN PERSONNEL REDUCED TO SUP_ID" asks Datatrieve to develop a record stream (A) with all of the supervisor IDs. Then for each supervisor ID, Datatrieve searches through all the PERSONNEL records again for matches on the ID field. When Datatrieve finds a match, it displays the ID, NAME, and DEPT of the employee.

To do this, the RSE must include a context variable, A, to refer to the SUP_ID of the first record stream. The context variable is then used in the Boolean expression ID = A.SUP_ID.

If you used the Boolean expression ID = SUP_ID, Datatrieve would consider SUP_ID to be a field in the records of the second record stream. Datatrieve would then find all employees whose personal ID is the same as their supervisor’s ID (that is, all employees who supervise themselves). The value expression A.SUP_ID unambiguously refers to a field value from records in the first record stream.

See Appendix A, "Name Recognition and Single Record Context" for more information about context variables.

When you use a PRINT statement to display a record stream, the order of the records is determined by the keys defined for the data file. However, you can use the SORTED BY clause of the RSE to impose a different sort order on the record stream.

For example, the records in PERSONNEL are already sorted by ID, the primary key for the data file. However, if you are interested in the employees for each department, you can sort the records by DEPT.

To break down each department into experienced workers and trainees, specify STATUS as an additional sort key. The following query sorts the first nine PERSONNEL records according to DEPT and STATUS:

DTR> PRINT FIRST 9 PERSONNEL SORTED BY DEPT, STATUS

                    FIRST       LAST            START             SUP
 ID     STATUS      NAME        NAME    DEPT    DATE     SALARY   ID

87465 EXPERIENCED ANTHONY    IACOBONE   C82   2-Jan-1973 $58,462 00012
90342 EXPERIENCED BRUNO      DONCHIKOV  C82   9-Aug-1978 $35,952 87465
99029 EXPERIENCED RAINED     PODERESIAN C82  24-May-1979 $33,738 87465
49001 EXPERIENCED DAN        ROBERTS    C82   7-Jul-1979 $41,395 87465
12643 TRAINEE     JEFF       TASHKENT   C82   4-Apr-1981 $32,918 87465
02943 EXPERIENCED CASS       TERRY      D98   2-Jan-1980 $29,908 39485
39485 EXPERIENCED DEE        TERRICK    D98   2-May-1977 $55,829 00012
84375 EXPERIENCED MARY       NALEVO     D98   3-Jan-1976 $56,847 39485
49843 TRAINEE     BART       HAMMER     D98   4-Aug-1981 $26,392 39485

DTR>

The SORTED BY clause overrides the order of the records in the data file, but it does not change the physical order of the records in the data file.

You can also sort a record stream according to a value expression based on a field value. For example, you could sort by the year of START_DATE by using the value expression FN$YEAR (START_DATE) as a sort key:

DTR> READY PERSONNEL
DTR> FIND FIRST 9 PERSONNEL SORTED BY
CON> FN$YEAR (START_DATE)
DTR> PRINT ALL ID, NAME, SALARY,
CON> (FN$YEAR (START_DATE))
CON> ("EMPLOYED"/"SINCE") USING 9999

         FIRST       LAST             EMPLOYED
 ID      NAME        NAME     SALARY   SINCE

00012  CHARLOTTE  SPIVA       $75,892   1972
87465  ANTHONY    IACOBONE    $58,462   1973
84375  MARY       NALEVO      $56,847   1976
00891  FRED       HOWL        $59,594   1976
39485  DEE        TERRICK     $55,829   1977
48475  GAIL       CASSIDY     $55,407   1978
90342  BRUNO      DONCHIKOV   $35,952   1978
99029  RAINED     PODERESIAN  $33,738   1979
87289  LOUISE     DEPALMA     $57,598   1979

DTR>

The SORTED BY clause lets you produce reports with data records divided into groups. In the last example, using the value expression FN$YEAR (START_ DATE) as a sort key lets you report on employees grouped by the year they were first employed. For more information on creating such control group reports, see Chapter 14, "Using the Report Writer". For information on Datatrieve functions such as FN$YEAR, see the VSI Datatrieve Reference Manual.

You can create a record in the data file with the STORE statement. You can also use the STORE statement to assign values to fields. When you enter a STORE statement followed by a domain name, Datatrieve prompts you for the values of each field in the record. If you enter a field list or the USING clause, Datatrieve prompts you to enter only the specified fields. Datatrieve does not prompt you to enter REDEFINES or COMPUTED BY fields.

To store records in a domain, you must first ready it for either write or extend access. If you choose write access, you can also print and modify records in the domain. Ready the domain with the shared option if you know other users might be storing, modifying, or erasing records in that domain at the same time you are. For example:

DTR> READY EMPLOYEES SHARED WRITE
DTR>

DTR> READY OWNERS WRITE
DTR> STORE OWNERS
Enter NAME: BILL
Enter BOAT_NAME: GLOOM
Enter BUILDER: DOWN EAST
Enter MODEL: 32T
DTR> FIND OWNERS WITH BOAT_NAME = "GLOOM"
[1 record found]
DTR> SELECT; PRINT

                 BOAT
   NAME          NAME         BUILDER     MODEL

BILL       GLOOM             DOWN EAST  32T

DTR>

When you respond to a Datatrieve prompt, you must supply a value, a space, or a Tab character, not a value expression. You cannot supply the name of a variable or a field and expect Datatrieve to use the value associated with the variable or the value associated with the field. Datatrieve interprets the name in either case as a character string literal and uses the literal as the value when making the assignment.

Datatrieve takes the following actions when you respond with a Tab and Return to a prompt from a STORE statement:

In the USING clause you specify only those fields you want to change. When you store values in the fields of a new record Datatrieve uses the values you assign to initialize the fields specified in the USING clause. To initialize the fields that you do not include in the USING clause, Datatrieve acts as if you responded with a Tab and Return to a prompt from a STORE statement. The following example shows the different actions Datatrieve takes when you assign a limited number of values with the USING clause of a STORE statement:

DTR> SHOW TEST_1
DOMAIN TEST_1 USING TEST_REC ON TEST1.DAT;

DTR> SHOW TEST_REC
RECORD TEST_REC USING
01 TOP.
   03 DEF_VAL1 PIC X(7)
      DEFAULT VALUE IS "DEFAULT".
   03 MISS_VAL1 PIC X(7)
      MISSING VALUE IS "MISSING".
   03 BOTH_1 PIC X(7)
      DEFAULT VALUE IS "DEFAULT"
      MISSING VALUE IS "MISSING".
   03 NEITHER_STR PIC X(3).
   03 NEITHER_NUM PIC 999.
   03 DEF_VAL2 PIC X(7)
      DEFAULT VALUE IS "DEFAULT".
   03 MISS_VAL2 PIC X(7)
      MISSING VALUE IS "MISSING".
   03 BOTH_2 PIC X(7)
      DEFAULT VALUE IS "DEFAULT"
      MISSING VALUE IS "MISSING".
;

DTR> READY TEST_1 WRITE
DTR> STORE TEST_1 USING
[Looking for statement]
CON> BEGIN
[Looking for statement]
CON>    DEF_VAL1 = "ONE"
CON>    MISS_VAL1 = "TWO"
CON>    BOTH_1 = "THREE"
CON> END
DTR> FIND TEST_1
[1 record found]
DTR> PRINT ALL

 DEF     MISS    BOTH   NEITHER NEITHER   DEF     MISS    BOTH
 VAL1    VAL1     1       STR     NUM     VAL2    VAL2     2

ONE     TWO     THREE             000    DEFAULT MISSING DEFAULT

DTR> STORE TEST_1
Enter DEF_VAL1: FOUR
Enter MISS_VAL1: FIVE
Enter BOTH_1: SIX
Enter NEITHER_STR: Tab
Enter NEITHER_NUM: Tab
Enter DEF_VAL2: Tab
Enter MISS_VAL2: Tab
Enter BOTH_2: Tab
DTR> FIND TEST_1;SELECT LAST; PRINT

 DEF     MISS    BOTH   NEITHER NEITHER   DEF     MISS    BOTH
 VAL1    VAL1     1       STR     NUM     VAL2    VAL2     2

FOUR    FIVE    SIX               000    DEFAULT MISSING DEFAULT

DTR>

There are two ways you can get Datatrieve to prompt you for values:

Having Datatrieve prompt you to enter values has the following advantages:

Using prompting value expressions within the USING clause of a MODIFY statement is a very flexible method for assigning values to fields.

In the following example, the double asterisk prompt means that the user is prompted to enter one field value that applies to all the records in the collection. The single asterisk prompt means that the user is prompted to enter a field value for each record:

DTR> SET NO PROMPT
DTR> READY YACHTS MODIFY
DTR> FIND YACHTS WITH BEAM = 0
[5 records found]
DTR> FOR CURRENT MODIFY USING
CON>    BEGIN
CON>      PRINT SPECS
CON>      LOA = **.LOA
CON>      DISP = *.WEIGHT
CON>      BEAM = *.BEAM
CON>      PRICE = PRICE * 1.1
CON>    END

       LENGTH
        OVER
 RIG    ALL   WEIGHT BEAM  PRICE

SLOOP   32     9,500  00
Enter LOA: 33
Enter WEIGHT: 12000
Enter BEAM: 10
SLOOP   32    11,000  00  $29,500
Enter WEIGHT: Tab
Enter BEAM: 11
SLOOP   31    13,600  00  $32,500
Enter WEIGHT: 15000
Enter BEAM: 12
SLOOP   35    23,200  00
Enter WEIGHT: Tab
Enter BEAM: 13
SLOOP   32    14,900  00  $34,480
Enter WEIGHT: Tab
Enter BEAM: 9
DTR> PRINT ALL

                               LENGTH
                                OVER
MANUFACTURER    MODEL     RIG    ALL   WEIGHT BEAM  PRICE

 METALMAST    GALAXY     SLOOP   33   12,000   10
 O’DAY        32         SLOOP   33   11,000   11  $32,450
 RYDER        S. CROSS   SLOOP   33   15,000   12  $35,750
 TA CHIAO     FANTASIA   SLOOP   33   23,200   13
 WRIGHT       SEAWIND II SLOOP   33   14,900   09  $37,928

DTR>

You must respond to a prompt with a value rather than a value expression. For example, if you want to increase a price field by ten percent and let Datatrieve do the calculation, you must use direct assignment.

Datatrieve will not let you enter PRICE * 1.1 in response to a prompt. If you are writing a procedure that needs this flexibility, you can prompt for part of the value expression. For example, you can prompt for the price increase and include the arithmetic calculation of the new value for PRICE in your Assignment statement (PRICE = PRICE * *."price increase").

In the USING clauses of STORE, you can use prompting value expressions to control the input to records in data files. You can use two forms of prompting value expressions: *.prompt and **.prompt. These value expressions let you control Datatrieve prompts for input.

Both forms of prompting value expressions require you to respond by entering values, not value expressions. You cannot enter the names of variables or fields, and you cannot enter expressions from Datatrieve tables or arithmetic, statistical, or concatenated expressions. You must enter numeric or character string literals appropriate to the data type of the field for which you are supplying a value. Do not enclose character string literals in quotation marks when you supply a value to a prompt. If you do, Datatrieve treats the quotation marks as part of the value.

If a *.prompt is part of a USING clause in a STORE statement, Datatrieve prompts you for a value each time it executes the statement. If the STORE statement is in a REPEAT, FOR, or WHILE loop, Datatrieve prompts you each time it executes the loop.

With the **.prompt, Datatrieve prompts you only once, regardless of how many times it executes the loop. The **.prompt is useful for assigning one value to a number of records when you have to assign unique values to other fields in each of those records.

When you modify records, you must ready the associated domain for modify or write access. Then perform the following five steps:

The following example shows you how to change one DEPT value in the PERSONNEL domain. In this case, you work with records directly from the domain and change all records containing the value. Note that you specify the record source and the record you want in the MODIFY statement itself:

DTR>  READY PERSONNEL MODIFY
DTR>  PRINT LAST_NAME, DEPT OF PERSONNEL WITH
DEPT = "F11"

  LAST
  NAME    DEPT
HOWL       F11
SCHWEIK    F11
HARRISON   F11
CHONTZ     F11

DTR> MODIFY PERSONNEL - 
CON> WITH DEPT = "F11" 
CON> USING DEPT = 
CON> "F12" 
DTR>
DTR>  PRINT LAST_NAME, DEPT OF PERSONNEL WITH
DEPT = "F12"

  LAST
  NAME    DEPT
HOWL       F12
SCHWEIK    F12
HARRISON   F12
CHONTZ     F12

Note that you cannot modify the value of an indexed key field if either of the following conditions exist:

Forming a collection and then using that collection as the record source for a modify operation is generally easier than trying to include the record selection syntax in the same Datatrieve statement that specifies fields and assigns values. The FIND statement forming the collection specifies the record source and does most of the work to select the records you want.

You can then use PRINT ALL to check the contents of the entire collection before and after the modify operation. You can also select a record and then use PRINT to display the same information for a particular record.

MODIFY [VERIFY [USING] validation-statement]

MODIFY field [,...] [VERIFY [USING] validation-statement]

MODIFY USING assignment-statement
[VERIFY [USING] validation-statement]

DTR> MODIFY USING FIRST_NAME = "CASSANDRA"
DTR>

FIND list-field
SELECT n
MODIFY item-field [VERIFY [USING] validation-statement]

MODIFY [ALL] list-item OF list

MODIFY ALL [VERIFY [USING] validation-statement]

MODIFY ALL field [, . . . ]
[VERIFY [USING] validation-statement]

MODIFY ALL USING assignment-statement [VERIFY [USING] validation-statement]

Section 8.6, ''Modifying Records in the CURRENT Collection'' showed that when you work with the CURRENT collection or a selected record, the MODIFY statement does not need to contain a record source or to identify which particular records to modify. You can display records and change values using relatively few keystrokes.

When you do not want the MODIFY statement to assume a CURRENT collection or a selected record for the modify operation, you have to specify both the record source and exactly which records you want to change as an RSE. You must include the RSE either in the MODIFY statement itself or in the FOR statement component of a compound statement that also includes the MODIFY statement. If you plan for a display of records before and after the modify operation, you must include PRINT statements as well.

Modifying records in an RSE is the best method to use when writing procedures. Usually, procedures contain compound statements, and you cannot use the Datatrieve statements that create and manipulate collections in compound statements. Writing compound statements that modify data is not difficult if you keep in mind the logical steps to a modify operation and make sure you include all of them in the statements you form.

You must first ready a domain with either modify or write access before you can modify any records it contains. Use the shared option if you want to let other users store, erase, or modify records in the domain at the same time you are accessing it.

There are three methods you can use to modify records. You can modify:

Using any of these methods, you can specify the fields you want to change. If you do not specify the fields to be changed, Datatrieve prompts you for all the record fields.

The following example illustrates how to modify records using a collection:

DTR> ! Change the value of the field CONTACT_NAME in one record
DTR> ! from the COLLEGES domain.
DTR> !
DTR> READY COLLEGES MODIFY
DTR> FIND COLLEGES WITH CONTACT_NAME CONTAINING "LYNCH"
[2 records found]
DTR> LIST ALL

COLLEGE_CODE : QUIN
COLLEGE_NAME : Quinnipiac College
CONTACT_NAME : George C. Lynch
ADDRESS_DATA :
STREET       :
TOWN         : Hamden
STATE        : NH
ZIP          : 06152

COLLEGE_CODE : STAN
COLLEGE_NAME : Stanford Univ.
CONTACT_NAME : Carol Lynch
ADDRESS_DATA :
STREET       :
TOWN         : Stanford
STATE        : CA
ZIP          :
DTR> !
DTR> ! Select the record to be modified from the collection.
DTR> !
DTR> SELECT 1
DTR> !
DTR> ! If you want to be prompted to enter a value for every field
DTR> ! in the record, simply enter the keyword MODIFY. If you
DTR> ! want to be prompted to enter values only for specific fields,
DTR> ! follow the keyword MODIFY with the names of those fields.
DTR> ! Remember to include a comma between field names if there is
DTR> ! more than one of them.
DTR> !
DTR> MODIFY CONTACT_NAME
Enter CONTACT_NAME: Hayward C. Dublin
DTR> LIST

COLLEGE_CODE : QUIN
COLLEGE_NAME : Quinnipiac College
CONTACT_NAME : Hayward C. Dublin
ADDRESS_DATA :
STREET       :
TOWN         : Hamden
STATE        : NH
ZIP          : 06152

DTR> !
DTR> ! Create a collection in which all records should have the
DTR> ! same values for a field. In the following example, two
DTR> ! JOB_HISTORY records have missing values in the SUPERVISOR_ID
DTR> ! field. Both records are for employees who have the same
DTR> ! supervisor, and you want to enter one value for DATATRIEVE
DTR> ! to store in both records.
DTR> !
DTR> FIND JOB_HISTORY WITH
CON> DEPARTMENT_CODE = "ELEL" AND JOB_CODE = "EENG" AND
CON> JOB_END MISSING
[2 records found]
DTR> PRINT ALL

EMPLOYEE JOB      JOB         JOB     DEPARTMENT SUPERVISOR
   ID    CODE    START        END        CODE        ID

 00238   EENG  2-Feb-1982                ELEL
 00428   EENG 10-Jan-1982                ELEL

DTR> !
DTR> ! In this case, enter MODIFY ALL followed by the field name or
DTR> ! names you want to change. DATATRIEVE prompts you once for
DTR> ! each field you specify and changes all the records in the
DTR> ! collection.
DTR> !
DTR> MODIFY ALL SUPERVISOR_ID
Enter SUPERVISOR_ID: 00356
DTR> PRINT ALL

EMPLOYEE JOB      JOB         JOB     DEPARTMENT SUPERVISOR
   ID    CODE    START        END        CODE        ID

 00238   EENG  2-Feb-1982                ELEL      00356
 00428   EENG 10-Jan-1982                ELEL      00356

If you modify records in collections, be careful when using the keyword ALL. As shown in Example 8.1, ''Modifying Records by First Creating a Collection'', you enter MODIFY ALL only when you want all the records in the collection to contain identical values in one or more fields. If you include an RSE in a MODIFY statement (MODIFY ... OF EMPLOYEES, for example), you get the same results for that RSE as you do for collections with MODIFY ALL—you are asking Datatrieve to store identical values in all the records.

If you want to put different field values in each collection record (without selecting each record in turn), you must set up a FOR loop. In the following sample statement, the keyword PRINT allows you to look at each record before and after you enter changes. Depending on what you want to display and change, you can specify field names following the keywords PRINT and MODIFY:

FOR CURRENT
  BEGIN
     PRINT
     MODIFY
     PRINT
  END

The following example modifies records directly from a domain rather than a collection using a FOR statement RSE:

DTR> SHOW CHANGE_SUPERS
!
! This procedure allows a user to change supervisor IDs for one
! or more current records in JOB_HISTORY. The user is prompted
! to enter the outdated supervisor ID and the department code.
! DATATRIEVE then displays a record, prompts the user to enter
! a new supervisor ID, and displays the changed record. The user
! is returned to the DTR> prompt if DATATRIEVE cannot find any
! records that meet the requirements in the FOR statement RSE.
!
PROCEDURE CHANGE_SUPERS
READY JOB_HISTORY SHARED MODIFY
FOR JOB_HISTORY WITH SUPERVISOR_ID = *."old supervisor" AND
  DEPARTMENT_CODE = *."department code" AND JOB_END MISSING
  BEGIN
    PRINT EMPLOYEE_ID, EMPLOYEE_ID VIA WHO_IS_IT, SUPERVISOR_ID
    MODIFY USING SUPERVISOR_ID = *."new supervisor"
    PRINT EMPLOYEE_ID, EMPLOYEE_ID VIA WHO_IS_IT, SUPERVISOR_ID PRINT SKIP
  END
FINISH JOB_HISTORY
END_PROCEDURE

DTR> :CHANGE_SUPERS
Enter department code: SALE
Enter old supervisor: 00200

EMPLOYEE                                      SUPERVISOR
   ID               EMPLOYEE NAME                 ID

 00208   Sciacca       Joe       V              00200
Enter new supervisor: 00504

EMPLOYEE                                      SUPERVISOR
   ID               EMPLOYEE NAME                 ID

 00208   Sciacca       Joe       V              00200
 00233   Mathias       Susan     N              00200
Enter new supervisor: 00497
 00233   Mathias       Susan     N              00497
               .            .            .
               .            .            .
               .            .            .

FOR PERSONNEL WITH ID = EMPLOYEE_VARIABLE
  MODIFY USING
    BEGIN
      PRINT ID, EMPLOYEE_NAME, DEPT, SUP_ID, SKIP
      FIRST_NAME = *."first name (all caps) or TAB character"
      LAST_NAME = *."last name (all caps) or TAB character"
      DEPT = *."department code (all caps) or TAB character"
      SUP_ID = *."supervisor ID number or TAB character"
      PRINT SKIP, ID, EMPLOYEE_NAME, DEPT, SUP_ID
    END

DTR> SHOW FOR_RSE_MODIFY
PROCEDURE FOR_RSE_MODIFY
SET ABORT
DECLARE EMPLOYEE_VARIABLE PIC 9(5).
EMPLOYEE_VARIABLE = *."employee ID number"
WHILE NOT ANY PERSONNEL WITH ID = EMPLOYEE_VARIABLE
  BEGIN
    PRINT SKIP
    PRINT "Invalid employee number."
    DECLARE GET_OUT PIC X(5).
    GET_OUT = *."any letter if you want to stop, TAB to try again"
    IF GET_OUT NOT = "" THEN
      ABORT "Exit from procedure" ELSE
        EMPLOYEE_VARIABLE = *."employee ID number"
  END
READY PERSONNEL MODIFY
SET NO ABORT
FOR PERSONNEL WITH ID = EMPLOYEE_VARIABLE
  MODIFY USING
    BEGIN
      PRINT ID, EMPLOYEE_NAME, DEPT, SUP_ID, SKIP
      FIRST_NAME = *."first name (all caps) or TAB character"
      LAST_NAME = *."last name (all caps) or TAB character"
      DEPT = *."department code (all caps) or TAB character"
      SUP_ID = *."supervisor ID number or TAB character"
      PRINT SKIP, ID, EMPLOYEE_NAME, DEPT, SUP_ID
    END
FINISH PERSONNEL
END_PROCEDURE

DTR>

Datatrieve always checks the record definition that applies to the record you are changing to ensure that new field values have the correct length and data type. It also applies any VALID IF clauses in the record definition to the changed field values. Datatrieve displays an error message and leaves the existing field value untouched if a modify operation tries to enter a value that the record definition does not allow.

The VERIFY clause of the MODIFY statement lets you supplement the validation requirements in the record definition. It can also help you enforce security measures for modification procedures. The validation statement can be a series of statements within a BEGIN-END block.

The VERIFY clause in the following example ensures that the first and last names entered for an employee begin with a capital letter:

DTR> FOR PERSONNEL WITH
CON>  ID = *."ID number for record being changed"
CON>    MODIFY VERIFY USING
CON>      BEGIN
CON>        WHILE FIRST_NAME NOT BT "A" AND "Z"
CON>          BEGIN
CON>            PRINT SKIP, "Invalid first name"
CON>            FIRST_NAME = *."first name using CAPS"
CON>          END
CON>        WHILE LAST_NAME NOT BT "A" AND "Z"
CON>          BEGIN
CON>            PRINT SKIP, "Invalid last name"
CON>            LAST_NAME = *."last name using CAPS"
CON>          END
                         .
                         .
                         .
CON>      END
DTR>

Note that Datatrieve does all verification only after all the data is entered for the record being modified.

DTR> ! Erase one of the DEGREES records for the employee with
DTR> ! ID number 00183.
DTR> !
DTR> READY DEGREES SHARED WRITE
DTR> FIND DEGREES WITH EMPLOYEE_ID = "00183"
[5 records found]
DTR> PRINT
No record selected, printing whole collection.

EMPLOYEE COLLEGE                DEGREE          DATE
   ID     CODE     DEGREE       FIELD           GIVEN

 00183           Associates Arts              3-Jul-1964
 00183           Masters    Elect. Engrg.    16-Aug-1965
 00183           Masters    Applied Math      3-Jul-1965
 00183           Bachelors  Arts             14-Jun-1965
 00183    MIT    Ph.D.      Elect. Engrg.    20-May-1965

DTR> !
DTR> ! The first record in the collection is the one to be erased.
DTR> !
DTR> SELECT 1
DTR> PRINT

EMPLOYEE COLLEGE                DEGREE          DATE
   ID     CODE     DEGREE       FIELD           GIVEN

 00183           Associates Arts              3-Jul-1964

DTR> ERASE
DTR> !
DTR> ! The SHOW CURRENT command indicates the selected record
DTR> ! has been erased. Remember, however, the address pointer
DTR> ! value for that record is still part of the collection.
DTR> ! Therefore, the value for "number of records" in the
DTR> ! collection always stays the same, no matter how many
DTR> ! records you erase from the data file.
DTR> !
DTR> SHOW CURRENT
Collection CURRENT
    Domain: DEGREES
    Number of Records: 5
    Selected Record: 1 (Erased)

DTR> PRINT ALL

EMPLOYEE COLLEGE                DEGREE          DATE
   ID     CODE     DEGREE       FIELD           GIVEN

 00183           Masters    Elect. Engrg.    16-Aug-1965
 00183           Masters    Applied Math      3-Jul-1965
 00183           Bachelors  Arts             14-Jun-1965
 00183    MIT    Ph.D.      Elect. Engrg.    20-May-1965

DTR> !
DTR> ! The first record in the PRINT ALL display is still
DTR> ! ordinal position 2 in the collection. Ordinal position 1
DTR> ! still belongs to the erased record. This is important to
DTR> ! remember if you are working with collections from which
DTR> ! you have erased records. If your SELECT statement
DTR> ! specifies an ordinal position that belongs to an erased
DTR> ! record, DATATRIEVE tells you it cannot find the record.
DTR> !
DTR> SELECT 1
Selected record not found.
DTR> !
DTR> ! If you want to erase all the records in the CURRENT
DTR> ! collection, simply enter ERASE ALL.
DTR> !
DTR> FIND DEGREES WITH EMPLOYEE_ID = "00489"
[3 records found] DTR> PRINT
No record selected, printing whole collection.

EMPLOYEE COLLEGE                DEGREE          DATE
   ID     CODE     DEGREE       FIELD           GIVEN

 00489           Bachelors  Arts             11-Jun-1983
 00489           Masters    Elect. Engrg.     9-Mar-1983
 00489    MIT    Masters    Applied Math     11-Jun-1983

DTR> ERASE ALL
DTR> PRINT ALL
DTR>

A REPEAT statement causes Datatrieve to execute the next statement a specified number of times. In response to the following statement, Datatrieve prompts you to enter field values for each of five records:

REPEAT 5 STORE EMPLOYEES

The number of times Datatrieve executes the subordinate statement can be specified as an expression rather than an integer; for example, REPEAT (FIELD1 * 4). If you use an expression, it must result in a positive whole number when Datatrieve evaluates it.

A FOR statement causes Datatrieve to execute the next statement on each of the records in an RSE. The following FOR statement causes Datatrieve to print each of the records in DEGREES that contain PRDU in the COLLEGE_CODE field:

FOR DEGREES WITH COLLEGE_CODE = "PRDU"
    PRINT

A BEGIN-END statement (also called a BEGIN-END block) causes Datatrieve to treat several statements as one statement. A BEGIN-END block defines the set of statements that must execute for each record in an RSE or each time a condition is true:

FOR EMPLOYEES WITH EMPLOYEE_ID = *."employee number"
  BEGIN
    LIST EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_ADDRESS
    MODIFY EMPLOYEE_NAME, EMPLOYEE_ADDRESS
    LIST EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_ADDRESS
  END

A BEGIN-END block inside a REPEAT statement causes Datatrieve to repeat an entire sequence of statements. If you want to store numerous records using selected fields, you can use a BEGIN-END block to repeat the sequence of prompting statements:

REPEAT 20 STORE JOBS USING
  BEGIN
    JOB_CODE = *.JOB_CODE
    JOB_TITLE = *.JOB_TITLE
  END

The keyword THEN joins two statements; it is most useful when you have two or three simple operations to perform in a loop:

FOR JOB_HISTORY WITH EMPLOYEE_ID = "00205"
  PRINT THEN MODIFY THEN PRINT

A WHILE statement tells Datatrieve to repeat the subordinate statement as long as a specified condition is true.

The condition specified in the WHILE statement takes the form of a Boolean (relational) expression. Datatrieve Evaluation of Compound Boolean Expressions tells you more about creating simple and complex Boolean expressions and using variables. In the following example, NUM LE 4 is a Boolean expression:

DTR> DECLARE NUM PIC 99.
DTR> DECLARE ITS_SQUARE COMPUTED BY NUM * NUM.
DTR> WHILE NUM LE 4
CON>   BEGIN
CON>     PRINT NUM, ITS_SQUARE
CON>     NUM = NUM + 1
CON>   END

     ITS
NUM SQUARE

00      0
01      1
02      4
03      9
04     16

DTR>

Note that NUM is a variable field name. You cannot specify a record field name on the left side of a Boolean expression when setting up a condition in a WHILE statement. If you need to put a record field name in that position, you must prompt the user to enter one (WHILE *.field name ...).

An IF-THEN-ELSE statement causes Datatrieve to execute one of two statements, depending on whether a condition is true or not. The THEN component contains the simple or compound statement to be executed when the expression is true. The ELSE component is optional and it specifies the simple or compound statement to be executed when the expression is false.

When you combine statements with IF-THEN-ELSE, you can omit the keyword THEN because Datatrieve knows an IF component is always incomplete. When you include an ELSE component, however, you cannot omit the keyword ELSE or put it on a line following the THEN statement. Type ELSE on the same line as the last part of the THEN statement. This is how you tell Datatrieve that you are not entering a simple IF-THEN statement.

The following example illustrates how to include an IF-THEN-ELSE statement in a procedure:

DTR> SHOW REVIEW_DATES
PROCEDURE REVIEW_DATES
READY JOB_HISTORY
!
DECLARE CUT_OFF_DATE USAGE DATE.
CUT_OFF_DATE = *."date six months ago"
!
FOR JOB_HISTORY WITH SUPERVISOR_ID = *."supervisor ID" AND
                JOB_END MISSING SORTED BY REVIEW_DATE
!
  IF REVIEW_DATE < CUT_OFF_DATE
  THEN PRINT EMPLOYEE_ID, EMPLOYEE_ID VIA WHO_IS_IT,
         REVIEW_DATE, " Needs a review" ELSE
  PRINT EMPLOYEE_ID, EMPLOYEE_ID VIA WHO_IS_IT,
         REVIEW_DATE, " Review up-to-date"
!
FINISH JOB_HISTORY
RELEASE WHO_IS_IT, CUT_OFF_DATE
END_PROCEDURE

DTR> :REVIEW_DATES
Enter date six months ago: 1/9/83
Enter supervisor ID: 00267

EMPLOYEE                                   REVIEW
   ID               EMPLOYEE NAME           DATE

 00497   Weist         Robert            30-Sep-1982   Needs a review
 00419   Clarke        Aruwa     Q       15-Dec-1982   Needs a review
 00355   Gutierrez     Joe                1-Jan-1983   Needs a review
 00184   Frydman       Louie     T        5-Jan-1983   Needs a review
 00464   Aaron         Alvin              9-Jan-1983   Review up-to-date
 00216   Lobdell       Arleen    Y       13-Apr-1983   Review up-to-date
 00244   Boyd          Ann       B       24-Apr-1983   Review up-to-date
 00283   Dallas        Paul               4-May-1983   Review up-to-date
 00200   Ziemke        Al        F       21-May-1983   Review up-to-date
 00501   Gramby        Terry              7-Jun-1983   Review up-to-date
 00241   Keisling      Edward             3-Jul-1983   Review up-to-date

DTR>

A CHOICE statement causes Datatrieve to execute one of a series of statements depending on the evaluation of a Boolean expression associated with each statement.

The CHOICE statement is a good substitute for nested IF-THEN-ELSE statements:

IF A > B THEN PRINT "A’s bigger" ELSE
   IF A = B THEN PRINT "A’s the same size" ELSE
      IF A < B THEN PRINT "A’s smaller" ELSE
         PRINT "A’s a strange duck"

CHOICE
   A > B THEN PRINT "A’s bigger"
   A < B THEN PRINT "A’s smaller"
   ELSE PRINT "A’s the same"
END_CHOICE

The ELSE clause is optional in a CHOICE statement. You can also omit the keyword THEN, although doing so makes the statement more difficult to read.

The following example illustrates the use of a CHOICE statement in a procedure.

DTR> SHOW REVIEW_DATES_TWO
PROCEDURE REVIEW_DATES_TWO
READY JOB_HISTORY
!
DECLARE CURRENT_DATE USAGE DATE.
CURRENT_DATE = "TODAY"
!
FOR JOB_HISTORY WITH SUPERVISOR_ID = "00267" AND
                JOB_END MISSING SORTED BY REVIEW_DATE
  CHOICE
      !
      ! GE 210 days is 7 months or more...
      !
      CURRENT_DATE - REVIEW_DATE GE 210 THEN
           PRINT EMPLOYEE_ID, REVIEW_DATE,
           "At least one month overdue"
      !
      ! BT 209 AND 180 days is between 7 and 6 months...
      !
      CURRENT_DATE - REVIEW_DATE BT 209 AND 180 THEN
           PRINT EMPLOYEE_ID, REVIEW_DATE,
           "A few weeks overdue"
      !
      ! BT 179 AND 150 days is between 6 and 5 months...
      !
      CURRENT_DATE - REVIEW_DATE BT 179 AND 150 THEN
           PRINT EMPLOYEE_ID, REVIEW_DATE,
           "Not overdue, but schedule"
      !
      ! No one else needs a performance review...
      !
      ELSE PRINT EMPLOYEE_ID, REVIEW_DATE,
           "Reviewed recently"
  END_CHOICE
!
FINISH JOB_HISTORY
END_PROCEDURE

DTR>

DTR>SHOW RTT
PROCEDURE RTT
READY YACHTS
FOR FIRST 3 YACHTS BEGIN
        PRINT CHOICE
                RUNNING COUNT NE 2 "NOT EQUAL 2"
                RUNNING COUNT EQ 2 "EQUAL 2"
                ELSE "NO MATCH"
                END_CHOICE
        END
END_PROCEDURE

DTR>:RTT
NOT EQUAL 2
NO MATCH
NOT EQUAL 2

DTR>SHOW RTT_WORKAROUND
PROCEDURE RTT_WORKAROUND
READY YACHTS
DECLARE X PIC 99.
FOR FIRST 3 YACHTS BEGIN
        X = RUNNING COUNT
        PRINT CHOICE
                X NE 2 "NOT EQUAL 2"
                X EQ 2 "EQUAL 2"
                ELSE "NO MATCH"
              END_CHOICE
        END
END_PROCEDURE

DTR>:RTT_WORKAROUND
NOT EQUAL 2
EQUAL 2
NOT EQUAL 2

You can create an infinite loop by setting up a condition that is always true. The following is a simple example:

WHILE 0 < 1
  PRINT "This is an infinite loop. Enter CTRL/C to stop it."

You can create the same sort of situation when you use expressions that contain variables whose values you do not control. Refer back to the examples for the WHILE statement in Section 9.5, ''Using the WHILE Statement''. If the values of the condition variables (MORE_RECORDS and NUM) were changed outside the BEGIN-END block subordinate to the WHILE statement, an infinite loop would result.

To define a procedure, enter the DEFINE PROCEDURE command at Datatrieve command level:

DEFINE PROCEDURE DTRUSR$DISK:[DTRUSERS]ROSSI.procedure-name

Datatrieve then prompts with DFN> to indicate that it expects a procedure definition. Enter the commands or statements that form the procedure definition. Datatrieve continues to prompt with DFN> until you enter the keyword END_PROCEDURE on a line by itself. For example:

DTR> DEFINE PROCEDURE BIG_YACHTS
DFN> READY YACHTS
DFN> FIND BIGGIES IN YACHTS -
DFN>      WITH LOA GT 40 SORTED BY BUILDER
DFN> PRINT ALL
DFN> END_PROCEDURE
DTR>

Datatrieve procedure definitions can be stored either in the DMU or in the CDO format dictionary. When defining a procedure, you must set default to a CDD/Repository dictionary directory, or you must specify a full dictionary path name in your procedure definition.

Some errors may occur during execution of the procedure. A typing error, for instance, can result in a syntax error in an otherwise correctly formatted command. If an error occurs during execution, Datatrieve prints an error message and terminates the procedure. You can correct the error by using an editor. Invoke the editor with the following command:

EDIT procedure-name

The EDIT command puts your entire DEFINE command in the edit buffer. You may have to repeat this process several times to take care of all the syntax mistakes in your procedure definition.

Creating procedures in stages can save you the frustration of searching through a large number of lines of input to find a missing quotation mark or period.

You invoke a procedure by preceding its name with the keyword EXECUTE or with a colon (:).

To invoke a procedure stored in the DMU format dictionary, you must have P (PASS_THRU), S (SEE), and E (EXECUTE_EXTEND) access to it. To invoke a procedure stored in the CDO format dictionary, you must have S (SHOW) access to the dictionary and to the procedure definition. You cannot invoke a procedure during an ADT, EDIT, or Guide Mode session. You cannot include a procedure in a domain, record, or table definition.

The content of a procedure determines where you can invoke it. In general, you can invoke a procedure anywhere you can use the commands or statements contained in the procedure. For example, if the procedure contains Datatrieve commands and statements, you can invoke it at the Datatrieve command level:

DTR> :BIG_YACHTS

You can invoke a procedure from the OpenVMS command level. For example:

$ DATATRIEVE "EXECUTE BIG_YACHTS

After Datatrieve executes the last command or statement in the file, you are automatically returned to the system prompt.

You can use the colon to execute a procedure from the OpenVMS level, but you must precede it with a double quote:

$ DATATRIEVE ":BIG_YACHTS

If you are running Datatrieve in a DECwindows environment and you want to invoke a Datatrieve procedure, you may want to use the DCL DATATRIEVE command with the /INTERFACE = CHARACTER_CELL qualifier. The benefit of doing this is that the Datatrieve main application window will not be displayed on your screen while the procedure is being executed.

The following example shows how the DATATRIEVE command would be used in the command file:

$ TYPE STOREEMP.COM
$ DATATRIEVE/INTERFACE=CHARACTER_CELL -
"EXECUTE CDD$TOP.PERSONNEL.STORE_EMPLOYEES"
$ PRINT/QUEUE=SYS$PRINT AUDIT.LOG
$ @STOREEMP.COM
              .         .         .
              .         .         .
              .         .         .
$

A procedure can contain any number of the following Datatrieve elements:

DTR> :BIG_YACHTS

                               LENGTH
                                OVER
MANUFACTURER    MODEL     RIG    ALL   WEIGHT BEAM  PRICE

 CHALLENGER   41         KETCH   41   26,700   13  $51,228
 COLUMBIA     41         SLOOP   41   20,700   11  $48,490
 GULFSTAR     41         KETCH   41   22,000   12  $41,350
 ISLANDER     FREEPORT   KETCH   41   22,000   13  $54,970
 NAUTOR       SWAN 41    SLOOP   41   17,750   12
 NEWPORT      41 S       SLOOP   41   18,000   11
 OLYMPIC      ADVENTURE  KETCH   42   24,250   13  $80,500
 PEARSON      419        KETCH   42   21,000   13

DTR>

DTR> DEFINE PROCEDURE BIG_YACHTS_RSE
DFN> BIGGIES IN YACHTS WITH LOA GT 40 SORTED BY BUILDER
DFN> END_PROCEDURE
DTR>

DTR> PRINT ALL :BIG_YACHTS_RSE

                               LENGTH
                                OVER
MANUFACTURER    MODEL     RIG    ALL   WEIGHT BEAM  PRICE

 CHALLENGER   41         KETCH   41   26,700   13  $51,228
 COLUMBIA     41         SLOOP   41   20,700   11  $48,490
 GULFSTAR     41         KETCH   41   22,000   12  $41,350
 ISLANDER     FREEPORT   KETCH   41   22,000   13  $54,970
 NAUTOR       SWAN 41    SLOOP   41   17,750   12
 NEWPORT      41 S       SLOOP   41   18,000   11
 OLYMPIC      ADVENTURE  KETCH   42   24,250   13  $80,500
 PEARSON      419        KETCH   42   21,000   13

If you use the Return key to hold Datatrieve back from processing the input, rather than the hyphen continuation character (see Section 1.8, ''Entering Long Command Lines''), you can turn off (but still get a CON> prompt) by entering the SET NO PROMPT command:

DTR>  READY FAMILIES
DTR>  PRINT FAMILIES WITH
[Looking for Boolean expression]
CON>  FATHER = "JIM" AND
[Looking for Boolean expression]
CON>  MOTHER = "ANN"

                     NUMBER    KID
  FATHER    MOTHER    KIDS     NAME    AGE

  JIM       ANN        2      URSULA    8
                              RALPH     4

DTR>  SET NO PROMPT
DTR>  PRINT FAMILIES WITH
CON>  FATHER = "JIM" AND
CON>  MOTHER = "ANN"

                     NUMBER    KID
  FATHER    MOTHER    KIDS     NAME    AGE

  JIM       ANN        2      URSULA    8
                              RALPH     4

DTR>

You can use the SET ABORT or SET NO ABORT commands to control what happens if Datatrieve encounters an error condition when processing a procedure.

When SET ABORT is in effect, Datatrieve exits a procedure when either of the following conditions is true:

If either of these conditions is true and SET NO ABORT is in effect, Datatrieve does not exit the procedure. It does abort the statement it is executing but continues processing any remaining statements and commands.

You may want SET ABORT in effect when your procedure readies domains that contain the data you want to access. If those domains cannot be readied, there is no point in continuing with the rest of the procedure. If SET NO ABORT were in effect, Datatrieve would produce error messages as it processed any statements referring to the domains. The default setting in Datatrieve is SET NO ABORT. You can ensure that SET ABORT is in effect by including that statement in the procedure definition.

On the other hand, SET NO ABORT should be in effect if your procedure prompts users to enter values for more than one record. In this case, you can expect someone to enter Ctrl/Z to keep an entry for one of the records from being stored. You want users to reenter the looping cycle so they can store or change more records. If SET ABORT is in effect, Datatrieve aborts the remainder of a procedure or command file when you enter Ctrl/Z.

The ABORT statement lets you specify an error condition appropriate for the operation you are performing. The ABORT statement terminates execution of the compound statement or statements containing it and can print a message explaining the termination.

Statements to control and specify error conditions can be numerous and complex in large scale applications. This section is designed only to introduce you to the topic. Some restrictions that apply to the ABORT statement are not listed here. For more detailed information on handling error conditions, read the sections on the SET command and the ABORT statement in the VSI Datatrieve Reference Manual.

You can invoke a procedure in a REPEAT statement to execute it a number of times or in a FOR statement to apply it to a record stream.

To repeat the entire procedure, enclose the procedure call or the procedure definition in a BEGIN-END block. For example, the following sequence of statements puts a procedure call in a BEGIN-END block and repeats the procedure 5 times:

DTR> REPEAT 5 BEGIN
[Looking for statement]
CON> :HEAVY_SLOOP
CON> END
DTR>

The following example includes a FOR statement and a BEGIN-END block in a procedure definition and invokes the procedure in a REPEAT statement:

DTR> SHOW HEAVY_SLOOP
PROCEDURE HEAVY_SLOOP
FOR YACHTS WITH BUILDER = *."MANUFACTURER"
   BEGIN
     IF RIG = "SLOOP" AND DISP GE 10000
     PRINT BOAT
   END
END_PROCEDURE
DTR> REPEAT 3 :HEAVY_SLOOP
Enter MANUFACTURER: CAL

                               LENGTH
                                OVER
MANUFACTURER    MODEL     RIG    ALL   WEIGHT BEAM  PRICE

 CAL          3-30       SLOOP   30    10,500  10
 CAL          35         SLOOP   35    15,000  11
Enter MANUFACTURER: PEARSON
 PEARSON      10M        SLOOP   33    12,441  11
 PEARSON      35         SLOOP   35    13,000  10
 PEARSON      36         SLOOP   37    13,500  11
 PEARSON      39         SLOOP   39    17,000  12
Enter MANUFACTURER: NAUTOR
 NAUTOR       SWAN 41    SLOOP   41    17,750  12

DTR>

If you invoke a procedure in a FOR statement, you must use the same technique: enclose the call or the procedure definition in a BEGIN-END block. For example:

FOR rse BEGIN :procedure-name END

If you use a procedure in a compound statement, do not include any commands or a FIND, SELECT, DROP, SORT, or REDUCE statement in that procedure. Datatrieve does not accept commands or any of these statements in BEGIN-END blocks or other compound statements; it accepts them only as simple statements.

In addition, when a FIND statement is in a procedure, Datatrieve does not display a message to tell you how many records it found for the collection. If Datatrieve finds no records that meet the specifications in the FIND statement RSE, a subsequent SELECT statement will produce an error message.

You can generalize procedures so that they operate on numerous domains. Be sure that the generalized procedure refers to an alias rather than to the domain name.

For example, you might want to keep separate domains for boats from different geographical areas, perhaps the domains WEST_YACHTS, EAST_YACHTS, and SOUTH_YACHTS (which of course have the same record definition and data file format).

When you ready each domain, rename it with an alias, using the AS clause in the READY command. To create the alias ALL_YACHTS for the domain WEST_YACHTS, respond to the DTR> prompt with this READY command: