******************************************************************
* *
* Stanford Data Center *
* Stanford University *
* Stanford, Ca. 94305 *
* *
* (c)Copyright 1994 by the Board of Trustees of the *
* Leland Stanford Junior University *
* All rights reserved *
* Printed in the United States of America *
* *
******************************************************************
SPIRES (TM) is a trademark of Stanford University.
Users working with SPIRES subfiles often need to access records in ways not covered by the standard searching and updating facilities. For instance, you may need to locate records using some record criterion that is not indexed. Or you may need to update all the records having a certain non-indexed element value but do not necessarily know the keys of the records. Or you may just want to examine all the records that have been added or updated in the subfile since the file was last processed in SPIBILD.
One common link between these three situations is that you do not know or have the keys to the records you wish to handle. If you did, you could use the standard "DISPLAY key", "TRANSFER key" and "UPDATE key" commands to work with these records one at a time (though that method would be very tedious if many records were involved). These situations can all be handled with Global FOR procedures, which allow you to work with groups of records based on criteria other than their keys or those element values that appear in indexes.
Global FOR allows you to sequentially process records in a particular class, whether it be the class of records added to the subfile today, the class of records in the current search result, the class of records that have been removed today, and so on. Each of the classes can be further subsetted, depending on values in the records themselves. Many commands can be used to work with these records, the primary ones being the familiar DISPLAY and TRANSFER commands, used with new options in Global FOR.
This manual will teach you about using Global FOR in your SPIRES activity. Global FOR is discussed briefly in the manual "SPIRES Searching and Updating", sections B.5.3.3, D.3 and D.4. That material is sufficient for many users; this manual is a reference of the complete capabilities, especially written for advanced users, file owners and programmers. Because you should understand the material in that manual to understand the information here, reading that manual is a prerequisite. From time to time, there are sections here that are more advanced (say, a section requiring some knowledge of SPIRES protocols) or generally more useful to file owners but these sections have been marked accordingly. Remember that if there is a term or subject you do not understand, you can issue the EXPLAIN command in SPIRES for information.
Many of the concepts and tools used to sequentially process records under Global FOR are also used in the sequential processing of parts of a record. Partial FOR processing is the subject of Chapter 12 of the manual "SPIRES Technical Notes".
Two points of terminology need to be explained. First, when the term "process" is used in this manual (most often appearing with the word "records"), it means "working with" records using the SPIRES commands available in Global FOR; when you process records, you may be displaying, transferring, removing or in some other way working with the records. On the other hand, the phrase "processing a file in SPIBILD" occasionally appears, referring to that procedure where records in the deferred queue are moved into the goal record tree and the indexes are updated accordingly. Thus, processing records in Global FOR and processing a file in SPIBILD are two different activities. In this manual, "processing" refers to records, not files, unless stated otherwise explicitly.
Related to the previous point, the second point of terminology concerns a shortcut used throughout SPIRES documentation. Most files are processed in SPIBILD every night automatically; the only records in the deferred queue on any given day are those placed there that day. Other files are processed more often (say, twice a day) or less often (once a month) by their owners. In all cases, for any file, the records in the deferred queue have been put there "since the file was last processed". In SPIRES documentation, the word "today" is often substituted for the quoted phrase in the last sentence. So if a discussion mentions "processing all the records added today", it is talking about the records added since the file was last processed, which may or may not mean only records added today.
In SPIRES manuals, examples of sessions are shown with prompts and messages from SPIRES as they actually appear on the terminal, usually in uppercase; commands you type are shown in lowercase. You may use upper or lower case interchangeably when you are actually using SPIRES. For example:
-OK to clear? ok
Here SPIRES types "-OK to clear?" and you type "ok".
In formal command syntax descriptions, uppercase letters denote command verbs or other command elements to be entered exactly as shown; a value for lowercase terms and characters must be supplied by you. For example:
SELECT subfile-name
To use this particular command, you type the command verb "SELECT" with the name of the desired subfile, for instance, "Restaurant":
-> select restaurant
where "->" is the prompt from SPIRES.
Brackets ([]) denote optional elements. Braces ({ }) indicate that you must specify one (and only one) of the alternatives within the braces. Within the braces or brackets, a vertical line (|) separates possible choices. Neither brackets nor braces are to be typed as part of the command. For example:
SHOW SPIRES MAIL [CLEAR|NOCLEAR]
could be entered as
SHOW SPIRES MAIL or SHOW SPIRES MAIL CLEAR or SHOW SPIRES MAIL NOCLEAR
depending on whether you want the CLEAR option. Here is another example, using braces:
DEQUEUE {record-key-value|ALL}
This command must be entered either as
DEQUEUE record-key-value
where you supply the "record-key-value," or as
DEQUEUE ALL
with the choice again governed by the desired result.
Cross references to other sections of the manual are denoted by brackets, as in "[See 4.]", which refers you to chapter 4 for additional information.
To use commands in Global FOR, you must first enter the Global FOR environment by issuing a "FOR class" command, where "class" specifies a group of records to be processed sequentially. There are about a dozen main classes, such as the class of added records or the class of records in a search result, and such classes can be further subsetted in order to find records in the class fitting some particular criterion.
Taking one of the sample classes above, suppose you want to work with all the records in a subfile that have been added today, that is, since the file was last processed. After selecting the subfile, you would issue the command
-> for adds +>
"Adds" is a Global FOR class -- "FOR ADDS" tells SPIRES that you want to work with the group of records that have been added recently and which are now in the deferred queue, that area housing updates to the file, including added and updated records as well as removal requests. Most Global FOR commands issued after this "FOR class" command will affect a record or records in this class. The "FOR class" command itself does not access any records; the real work is done by record processing commands issued under Global FOR, as shown below.
When you enter the Global FOR mode, the SPIRES prompt changes from "->" to "+>". The plus sign ("+") indicates that you are in Global FOR mode and reminds you that some SPIRES commands act differently -- these commands are the Global FOR record processing commands discussed later. [See 5.] However, most other SPIRES commands are unaffected and can still be issued, as can any other system commands, such as WYLBUR or MILTEN commands.
Now that you have told SPIRES which records you want, you can begin to sequentially process them. For instance, you can DISPLAY the records, or TRANSFER and UPDATE them, or REMOVE them, DEQUEUE them, STACK them, or use some combination of these commands. Several other commands are also available for processing records in Global FOR. Many of these commands, when used outside of Global FOR, require the key value of the record, e.g., "DEQUEUE 191" or "TRANSFER GA.JNK". Under Global FOR, you will not specify the key at all, because you have told SPIRES in the "FOR class" command exactly which records you want to process. However, in Global FOR, there are other new options available for those commands, some of which are demonstrated below.
You now want to process the added records in the subfile:
-> for adds
+> display
<--- The first record in the class of added records is displayed.
+> display
<--- The next record is displayed.
+> transfer *
<--- You transfer the record you've just seen ("*" indicates
"the current record being processed") and modify it.
+> update
+> show keys rest
<--- The key values of all the rest of the records in the class
are displayed.
+> endfor
-End of global FOR
->
The ENDFOR command tells SPIRES to remove you from the Global FOR mode. SPIRES may automatically eject you from Global FOR: suppose that instead of issuing the ENDFOR command after the "SHOW KEYS REST" you had done the following:
+> display <--- asking to see the next record in the class -End of global FOR ->
Because your previous command had processed all the "REST" of the records in the class, there were no more to process when the DISPLAY command was issued. SPIRES tells you that there are no more records to process by removing you automatically from Global FOR. You can start over again with another "FOR class" command, or simply continue with other commands.
The remainder of this document discusses each aspect of Global FOR in detail. The "FOR class" commands, along with various options, are discussed in the next three chapters. The Global FOR commands that process records, such as DISPLAY, SHOW KEYS and TRANSFER, are explained in chapter 5. Two other Global FOR commands, SET SCAN and SHOW LEVELS, are explained in chapter 6. Returning from the Global FOR environment, either by an ENDFOR command or an automatic ejection, is discussed in chapter 7. Further general information about using Global FOR is included in the last chapter, chapter 8.
Outside of Global FOR, most SPIRES commands used to process records of a subfile are key-dependent; that is, you must know the record's key value to process it. For instance, the MERGE command requires the key value of the record you wish to update. The beauty of Global FOR is that you do not need to know the key values of the records you want to process. Instead, the "FOR class" command defines a group of records to be processed by subsequent commands under Global FOR, and SPIRES determines the record's key values for you. All Global FOR processing begins with a "FOR class" command.
A "FOR class" command can define a very large, general set of records (such as FOR ADDS, which accesses all records in the subfile's deferred queue that have been added) or a smaller subset of records from a larger set that fit very specific criteria. (You can, for instance, examine only added records that have more than one occurrence of a telephone number element.) WHERE clauses, VIA clauses, and SET SCAN commands can all be used to subset a "FOR class" command.
This chapter will discuss the "FOR class" command without WHERE or VIA clauses, explaining each of the classes that can be specified in the command. Chapter 3 will discuss the VIA clause, which provides an alternate source for record keys. The WHERE clause, which is a very powerful option for modifying the class of records, is discussed in Chapter 4. The SET SCAN commands, which normally restrict the class of records to be processed, are issued after a "FOR class" command, and will not be discussed until Chapter 6.
A "FOR class" command can be very simple or very complex, depending on whether or not VIA or WHERE clauses are used. Here is the general syntax of the FOR command:
FOR class [VIA access-class] [WHERE criteria-clause]
There are six retrieval classes, ten access classes, and two special classes, thus making a total of eighteen different values for "class" in the "FOR class" command. The first sixteen are known as the main classes. Only the first five access classes are valid in the VIA clause, but all of them may also be specified as "class". [See Chapter 3 for an explanation of the VIA clause, and Chapter 4 for a discussion of the WHERE clause.]
Here are the different values for "class":
Each of these classes is discussed completely in the rest of this chapter.
When records are added or updated in a subfile, SPIRES places them in the deferred queue, where they stay until the file is next processed, usually sometime overnight. During processing, the records in the deferred queue are moved into the goal record data set, or "tree", where the goal records already in the subfile are kept. Because the FOR SUBFILE command prepares SPIRES to examine records in both the tree and the deferred queue, it allows you to sequentially process all records currently in the subfile in their most recent form. Thus, if a record has not been updated since the file was last processed, it will come from the tree; if it has been updated, it will come from the deferred queue; if it has been added, it will come from the deferred queue; and if it has been removed, it will not be processed at all.
The FOR SUBFILE command has the following syntax:
FOR SUBFILE [VIA access-class] [WHERE criteria-clause]
Both the VIA and WHERE clauses are discussed later in this manual. [See Chapter 3 and Chapter 4.]
Records processed under FOR SUBFILE are processed in ascending key order, the order in which they are stored in the subfile. For example, if SPIRES assigns "slot numbers" as keys for the subfile (each added record receives the next available number in the series 1, 2, 3, etc.), then record 15 is stored in the tree (and in the deferred queue if they are there) before record 16, but after record 14. Similarly, if the keys of the records are dates stored in their special internal form by SPIRES, then "March 1980" would be stored before "April 1980" and after "February 1980" in the tree or deferred queue. Records processed under FOR SUBFILE are thus processed in ascending key order unless a VIA clause or a SET SCAN BACKWARD command is in effect. [See 3, 6.2.1.]
For example, with the SHOW KEYS command, which displays the key values of records in the class, you do the following processing:
-> select people -> for subfile +> show key first AA.CSB +> show key next AC.JAM +> show key next AD.MQC +>
After issuing the FOR SUBFILE command, you receive the Global FOR prompt ("+>") telling you that SPIRES is ready for any Global FOR commands you want to issue. The SHOW KEY FIRST command, a Global FOR command, processes the first record in the class, whose key is "AA.CSB". Then SHOW KEY NEXT processes the next record in the class, whose key is "AC.JAM", and so forth. [See 5.9 for details on the SHOW KEYS command, used in many of the examples of this chapter.]
FOR SUBFILE should be used when you must guarantee that record updates are taken into account when records are examined in Global FOR. Since SPIRES is examining both the tree and the deferred queue, more processing occurs in FOR SUBFILE operations than in FOR TREE ones.
Because FOR SUBFILE will cause SPIRES to process all the records in the file, you might be processing thousands of records, if the subfile is a very large one. A WHERE clause, which causes SPIRES to examine each record for particular information, can increase the processing costs substantially. [See 4.] You can limit the subset of records examined by using a SET SCAN command. [See 6.]
Under FOR SUBFILE, SPIRES examines both the deferred queue and the tree simultaneously, processing the latest copy of all records in the subfile. On the other hand, the FOR TREE command prepares SPIRES to sequentially process records in the subfile without looking at records in the deferred queue. No added records will be processed, nor will the updated copies of any records; any records that have been removed since the file was last processed will still be in the tree, so they will be processed by commands under FOR TREE.
In the tree, records are stored in ascending key order, so records processed under FOR TREE are processed in that order unless a VIA clause or a SET SCAN BACKWARD command is in effect. [See 3, 6.2.1.]
The syntax of the FOR TREE command is
FOR TREE [VIA access-class] [WHERE criteria-clause]
The VIA and WHERE clauses are discussed later in this manual. [See 3, 4.]
-> select people -> for tree +> show key first AA.AJM +> show key next AA.CSB +> show key next AC.JAM +>
Here you are examining the keys of records in the tree beginning with the first one. If you compare this example to the example discussed under FOR SUBFILE [See 2.2.] you will notice that the first key shown under FOR SUBFILE was "AA.CSB", which is the second key here. Assuming that these two examples were created the same day, that indicates that the record whose key is "AA.AJM", which is in the tree, has been removed since the file was last processed and thus is not processed under FOR SUBFILE. Since the removed record remains in the tree until overnight processing, it is processed under the FOR TREE command.
The fact that FOR TREE processing does not access the deferred queue is not a disadvantage, since other Global FOR commands can. It is very useful that it does not, in fact. Occasionally you may need to examine the old version of a record that has been updated or removed since the file was processed. If the FOR TREE command did not exist, you could only access the tree copy by dequeuing the update or removal request, which would not be very convenient. [See 3.2, which describes a procedure for processing the old version of a record.]
If you do need to use the latest copy of records in the subfile, taking added, updated and removed records into account, you can use FOR SUBFILE. [See 2.2.] However, processing records under FOR SUBFILE is slightly more expensive than under FOR TREE and is unnecessary if there are no records in the deferred queue. Generally, unless many records have been added, updated or removed, FOR TREE will suffice. If you frequently process all or a number of records in a subfile for report generation or other reasons, you should coordinate that activity with the processing of the file in SPIBILD so that you can take advantage of FOR TREE processing.
Because FOR TREE means that you will be processing the entire goal record data set, then if the subfile is a very large one, you might be processing thousands of records. That can be especially expensive if you have a WHERE clause on the FOR TREE command, causing SPIRES to examine each record for some particular criteria. [See 4.] You may be able to use the SET SCAN command to subset the class of records being processed at less cost than a WHERE clause. [See 6.]
If the records you are processing under FOR TREE retrieve data from records in other subfiles or record-types (through phantom elements or subgoal processing), SPIRES will examine deferred queues for the latest copy of those records. In other words, FOR TREE means "tree-only" processing for the goal records of the selected subfile, not any subgoal records.
With the FOR DEFQ command, you can sequentially process all records that have been added or updated since the file was last processed. It is useful when you want to review the major updating activity of the day.
Here is the syntax of the FOR DEFQ command:
FOR DEFQ [VIA access-class] [WHERE criteria-clause]
The VIA and WHERE clauses are discussed later in this manual. [See 3, 4.]
For example,
-> select restaurant
-> for defq
+> display all
<--- All updated and added records are displayed.
+>
Note that the DISPLAY command here operates differently than outside of Global FOR. The ALL option, available under Global FOR, specifies that all records in the class should be processed. [See 5.2.7, 5.3.]
Although each record processed under FOR DEFQ is either an added or an updated record, SPIRES does not tell you which type each is. If it is important that you know, then FOR ADDS and FOR UPDATES can be used separately instead. [See 2.5, 2.6.]
Records are processed in key order under FOR DEFQ:
-> select restaurant -> for defq +> show keys all 10 24 39 76 +>
The records are not processed in the order in which they were modified or added but in the order in which they are stored in the deferred queue, which is ascending by key.
It may at first seem unusual that FOR DEFQ, which by name would seem to access all the record information in the deferred queue, actually accesses only the added and updated records. After all, the deferred queue also contains information about removed and merged records. As it happens, merged records are treated as updated ones in the deferred queue. Though you only have part of a record in your active file when you issue a MERGE command, SPIRES gets the latest version of the record, merges your new information into that copy, and puts the entire record into the deferred queue as an updated record. So actually the FOR DEFQ command accesses all added, updated and merged records.
Records being removed are treated quite differently, however. Unlike "adds" and "updates", where SPIRES processes an entire record, you specify only a key with the REMOVE command to cause a record to be removed. So for a removal request, the deferred queue has only the record's key. "Removes" are thus quite different from adds or updates and so are handled separately. [See 2.7.]
The records accessed by a FOR ADDS command are a subset of the records accessed by the FOR DEFQ command -- specifically, the records that have been added to the subfile since the file was last processed.
The syntax of the command is:
FOR ADDS [VIA access-class] [WHERE criteria-clause]
The VIA and WHERE clauses are discussed in later sections of this manual. [See 3, 4.]
The following example demonstrates the use of the FOR ADDS command:
-> for adds +> in active display all +> dequeue all -Dequeued: 17 records +>
After placing copies of all of the records into the active file, the user has dequeued all the added records. The records are still retrievable, however, using the FOR TRANSACTIONS command. [See 2.8.]
What about records that are added and then updated later that day? They are still considered added records to SPIRES. Whether a record is considered an add or an update depends on its relationship to the tree, not on what commands you have issued to place it in the deferred queue. A record in the deferred queue with a key matching that of a record in the tree is an update; if there is no tree version, it is an add.
Records that are added and then dequeued or removed can not be accessed under the FOR ADDS command, since they are no longer being added to the subfile, nor under the FOR REMOVES command. They can be accessed through the FOR TRANSACTIONS command. [See 2.7, the FOR REMOVES command, 2.8, the FOR TRANSACTIONS command.]
Remember that the records will be processed by key order, not by the order in which they were added. The two orders would presumably be the same if the subfile were one in which SPIRES assigned the keys numerically (a slot subfile).
Like the FOR ADDS command, the FOR UPDATES command accesses a subset of records accessible through the FOR DEFQ command -- in this case, all the updated records.
Like other "FOR class" commands, the syntax for this command is:
FOR UPDATES [VIA access-class] [WHERE criteria-clause]
The VIA and WHERE clauses are discussed in later chapters. [See 3, 4.]
-> for updates +> show keys 3 17 113 298 +>
An "update" is the latest copy from the deferred queue of a record that already exists in the tree. A record that is removed and added again later that day (with the same key as before) is also considered an updated record, not an added one or a removed one.
Records that have been added and then updated later in the day can be accessed by the FOR ADDS command, not the FOR UPDATES command. Records that have been updated and then either removed or dequeued will not be accessed by the FOR UPDATES command; FOR TREE can be used to access the original copy. The dequeued, updated copy or copies can be accessed with the FOR TRANSACTIONS command. [See 2.8.]
The records accessed by the FOR UPDATES command are processed in key value sequence (as shown in the example above), not the order in which the records were updated.
Records in the tree that have been removed by a REMOVE command are treated quite differently than adds or updates. While at least one entire record resides in the deferred queue for each record being added or updated, only the record key is in the deferred queue for a removal request. Since only limited information is available, those Global FOR commands that work with whole records cannot be used under FOR REMOVES. The REMOVES class contains the keys of only those records in the tree that have been removed. Keys of records that have been added and then removed that day are not included. Such records can be resurrected using the FOR TRANSACTIONS command. [See 2.8.]
The syntax of the FOR REMOVES command is:
FOR REMOVES [VIA access-class] [WHERE criteria-clause]
VIA and WHERE clauses are discussed in detail elsewhere. [See 3, 4.]
-> for removes +> stack all -Stack: 6 RECORDS +> for removes +> display next -End of global FOR ->
The only Global FOR record processing commands that can be issued under FOR REMOVES are DEQUEUE, SHOW KEYS, SKIP and STACK. [See 5.] All other Global FOR commands require access to the record data itself; all that is available here under Global FOR is the record key. If you try to use a "forbidden" command, such as DISPLAY, under FOR REMOVES, you will be ejected from Global FOR mode, as shown above. There is no next record that can be displayed, so SPIRES ejects you from Global FOR mode.
WHERE clauses are allowed on a FOR REMOVES command; however, the only element they can refer to is the record key. For example, if the key of a record is an element called ID, and you want to know the keys of removed records that are higher than 50:
-> for removes where ID > 50 +> show keys all 78 96 145 +>
However, if the record also has a DATE-ADDED element, it could not be used in the WHERE clause:
-> for removes where date-added after july 1979 +> dequeue all -End of global FOR ->
There is a way to examine and process records being removed, which will be explained later. [See 3.2.] You might want to compare the SET SCAN commands with the WHERE clause in a case such as the previous example. [See 6.2.]
Suppose you have just added a record to a subfile and now decide to update it. You make changes, issue an UPDATE command and then realize you should not have made the changes. Can you access the original added record? The obvious answer would seem to be that you could use the FOR ADDS command. However, as you learned in the previous two sections, the FOR ADDS command would provide you with the latest copy of the added record, which is the updated copy that you do not want.
The command that is useful here is FOR TRANSACTIONS, which accesses all the different versions of added and updated records since the file was last processed.
The syntax of the FOR TRANSACTIONS command is:
FOR TRANSACTIONS [VIA access-class] [WHERE criteria-clause]
A special group of VIA clauses are allowed on the FOR TRANSACTIONS command. [See 3.9.] The WHERE clause is discussed later. [See 4.]
-> for transactions +> show keys all 19 19 25 19 +>
Under FOR TRANSACTIONS, the records are generally processed in the order that the transactions were made, from oldest to newest, and not in key order as under most of the other "FOR class" commands. (Records may not be in exact chronological order if more than one user is updating the subfile at one time.) However, the FOR TRANSACTIONS VIA "access-class" do process the records in key order. [See 3.9.]
The example above shows that there are three different versions of record 19 and one of record 25 in the deferred queue. The file owner can see the sequence of subfile updating commands that created the records processed under FOR TRANSACTIONS by issuing the SHOW SUBFILE TRANSACTIONS command. It tells the file owner what time a record was processed, what account processed it, and so forth, so it is very handy to use with the FOR TRANSACTIONS command. It is possible that none of the three copies of record 19 is the latest version -- the record may have since been dequeued or removed, a possibility not shown under FOR TRANSACTIONS. However, if any version is the latest copy of a record, it will generally be the last version processed under FOR TRANSACTIONS.
That fact can lead to a problem. Suppose only one record has been updated in a subfile:
-> for transactions +> show keys all 19 +> transfer * <--- "*" refers to the current record processed +> update +> show keys next 19 +> show keys all 19 19 +>
The example shows that record 19 was the only record in the transactions class when FOR TRANSACTIONS processing began. However, once record 19 was transferred and updated, the new version also joined the class of transactions, becoming the "next" record to be processed. Thus, each time you update a record in the class of transactions, there is one more record put into the class. Because of this, you should beware of processing the same records over and over under FOR TRANSACTIONS. [See 5.4 for details on the TRANSFER and UPDATE commands in Global FOR.]
The FOR TRANSACTIONS command is particularly useful when you have added records during the day and then accidentally dequeued, updated or removed them. Because you can easily track all the record modifications made during the day, you can use FOR TRANSACTIONS processing to create audit trails. It is also useful on the rare occasion when the deferred queue's tree has been damaged.
The FOR TRANSACTIONS command is actually a short form of the command FOR DEFQ VIA TRANSACTIONS. Since the DEFQ class does not contain the keys of removed records, they are not processed under FOR TRANSACTIONS. However, the keys of removed records later dequeued can be accessed using the FOR REMOVES VIA TRANSACTIONS command. [See 3.4.]
The FOR RESULT command lets you process records in the current search result. Its syntax is:
FOR RESULT [WHERE criteria-clause]
No VIA clause is allowed on the FOR RESULT command, which is actually a short form of the command FOR SUBFILE VIA RESULT. [See 3.1.] The WHERE clause is discussed in a later chapter. [See 4.]
Suppose you want to remove all records in the RESTAURANT subfile that were added before 1984 and that have not been updated since then:
-> select restaurant -> find date-added before 1984 and date-updated before 1984 -Result: 3 RESTAURANT(S) -> for result +> remove all -Removed: 3 records +>
The above method is much more efficient than another way, using a WHERE clause:
-> select restaurant
+> for tree where date-added before 1984 and
date-updated before 1984
+> remove all
-Removed: 3 records
+>
Although the second method took one less command than the first, it required considerably more computing resources. In the first example, searching an index (which is very efficient) saves SPIRES from having to examine each record in the tree individually. The difference is similar to the difference between using an index in a book to find certain information and looking page by page through the book to find the same information. Obviously it is more efficient to use the index first, if possible. Remember that in the FIND command in the first example, DATE-ADDED is an index name; in the WHERE clause in the second, it is an element name. Obviously, if there were no DATE-ADDED index, the index search could not be done.
The FOR RESULT command is useful not only as an updating tool but also as a searching tool. If you have a search result that you want to subset further using unindexed information, you can use a WHERE clause with the FOR RESULT command. Remember that the ALSO command can often be used in this situation more easily, but not, in many cases, as efficiently. [See 8.3.]
Under FOR RESULT, records are processed in the order in which they would appear if the TYPE command were issued, an order which may seem haphazard. If you want the records to be processed in a particular order (key order, for instance), you can use the SEQUENCE command to arrange the result. The stack created by the SEQUENCE command can then be processed under the FOR STACK command. [See 2.10.]
The FOR RESULT command always accesses the latest copy of each record in the result. Therefore, if a record in the result has been removed since the file was last processed, the record will not be processed under the FOR RESULT command.
It is not necessary that a result exist when you issue the FOR RESULT command; a search result can be established or changed under a FOR RESULT command. If you change the result, subsequent Global FOR commands will process the new result, starting from the "top" of the result.
-> show result -No result exists -> for result +> find composer beethoven -Result: 93 SELECTIONS +> remove all -Removed: 93 records +> find composer brahms -Result: 2 SELECTIONS +> remove all -Removed: 2 records +>
A stored result can often be processed directly (without having to issue the "FIND @RESULT.name" command) by using the FOR SET command. [See 2.11.]
When you want to sequentially process records in a stack, you use the FOR STACK command:
-> select restaurant -> find cuisine chinese -Result: 16 RESTAURANTS -> sequence name -Stack: 16 RESTAURANTS -> for stack +> transfer first +>
In the example, after creating a stack of sequenced records, you begin using the TRANSFER command [See 5.4.] so that you can update the records in the stack one at a time.
The syntax of the FOR STACK command is:
FOR STACK [WHERE criteria-clause]
The WHERE clause is discussed later. [See 4.] No VIA clause is allowed on the FOR STACK command. [See 3.] The FOR STACK command operates similarly to the FOR RESULT command, except that a stack rather than a result will be processed. The stack does not have to exist prior to the FOR STACK command, but may be created (or changed) after the FOR STACK command is issued. If the stack is changed by creating or restoring a new stack, then subsequent Global FOR commands will begin processing the new stack from the "top", with one exception. The STACK command when issued while a FOR STACK command is in effect does begin creating a new stack, but Global FOR commands continue processing the original stack until that stack is exhausted or until an ENDFOR command terminates the Global FOR processing. (The TYPE or SEQUENCE commands will end Global FOR processing automatically in such cases, and process records in the new stack.) [See 5.8.]
The FOR STACK command is equivalent to FOR SUBFILE VIA STACK, meaning that all records processed will be in their latest version. [See 3.2.] A stored stack can be processed directly, without restoring it first, by using the FOR SET command. [See 2.11.]
The syntax of the FOR STORED command is:
FOR STORED setname [WHERE criteria-clause]
where "setname" is a stored result or stack.
This Global FOR command can only be used within SPIBILD. Refer to the document "File Management" or online, type EXPLAIN FOR STORED.
When you use a generated set of records (which may have been sorted by the SPISORT program), you can access that set later with a FOR SET command:
FOR SET setname [DIRECT|UNFILTERED] [WHERE criteria-clause]
where "setname" is the name of the ORVYL data set created by the DEFINE SET and GENERATE SET commands [See 5.12.] or is a stored result or stack. If the set is stored as an ORVYL data set under some account other than your own, then use the form "FOR SET ORV.gg.uuu.setname" where "gg.uuu" is the account number under which the set is stored. A WHERE clause can be appended to the FOR SET command but not a VIA clause. [See 3, 4.]
The DIRECT option tells SPIRES not to retrieve the goal record that a sort entry points to, but instead to use the data within the sort entry itself. You would use this option for processing a "direct set" previously created with the "DEFINE SET setname DIRECT" command. (Direct sets are a way of increasing the efficiency of large sorting applications, and of applications where the same set might be sorted multiple times for multiple reports. For more information see Chapter 1 of "SPIRES Technical Notes" or EXPLAIN DIRECT SET.)
The UNFILTERED option may be used to tell SPIRES not to use the "path information" in the set to filter out undesired element occurrences. [See the manual "SPIRES Technical Notes" for more details on the use of path information with the FOR SET command.]
The FOR SET command can be issued after the set has been defined and generated (and then usually sorted by the SPISORT program). Under FOR SET, records will normally be processed in their sorted order.
The "setname" can also be a stored result or stack, in either the form "RESULT.name" or "STACK.name" where name is the given name of the stored result or stack. (Again, the prefix "ORV.gg.uuu." can be added if you are using a result or stack stored under another account. If you are using a temporary result or stack, use the "TEMPORARY RESULT.name" or "TEMPORARY STACK.name" form.) Thus, you do not have to restore the stack or "find" the stored result again before issuing the "FOR class" command. The equivalent command in SPIBILD is: FOR STORED setname. [EXPLAIN FOR STORED COMMAND.]
Not all stored results can be used by the FOR SET command. Only a result stored from non-structured pointer groups is allowed. The file definition must be examined to determine whether pointer groups are structured or not, though the presence of sub-indexes and qualifiers, identified by the SHOW INDEXES command, are a good indication that they are.
Here is an example:
-> select albums -> for set stack.brahms +> in active show keys all +>
The FOR SET command is equivalent to FOR SUBFILE VIA SET, meaning that you will get the latest copies of records when you process them under this command. To access the tree copies rather than the latest ones in the deferred queue, you can issue the command "FOR TREE VIA SET setname". [See 3.3.]
Because stored stacks and results do not contain element path information, be sure that you do not try to process them under a FOR SET using a format requiring element path information. [See the manual "SPIRES Formats" for information on the PATH and NPATH options.]
The FOR RESIDUAL DATA command is a file owner tool that is designed for use on rare occasions in order to facilitate recovery when there has been file damage. It can be used only if the records in the subfile have been REMOVED to a residual data set. [See "SPIRES File Definition".] Generally it is not useful for regular file activity. It provides an alternate way to retrieve goal records without going through the tree.
The syntax of the command is like that of other "FOR class" commands:
FOR RESIDUAL DATA [WHERE criteria-clause]
A WHERE clause can be appended to the command, but not a VIA clause. [See 3, 4.]
This command is not recommended for general use because it is possible that two versions of the same record may be in the residual data set. Usually this does not happen, but there is no guarantee that it will not. When you access records through the tree, the tree knows where the correct version is. Avoiding the tree by using the FOR RESIDUAL DATA command means that the same record in two versions could be processed twice.
If you do need to use this command, you can use the alternate form FOR SUBFILE VIA RESIDUAL DATA to insure that you get the latest version of each of the records processed, though duplicates may still occur. [See 3.5.] If you issue the command for a subfile whose records are not REMOVED, subsequent Global FOR commands will fail.
The FOR GOAL command is actually a short form of the command FOR SUBFILE VIA TREE. [See 3.6 for a detailed description.]
When you want to sequentially process all the goal records having elements with values in a particular index, use the FOR INDEX command:
FOR INDEX index-name [(SEQUENCE element-list)] [WHERE clause]
where "index-name" is a search term from the list displayed by the SHOW INDEXES command. The WHERE clause is discussed later. [See 4.] The SEQUENCE clause is discussed below. No VIA clause is allowed. [See 3.]
Record sorting is often done in SPIRES with the SEQUENCE command or with the SPISORT program. For both of these processes, SPIRES must examine each goal record being sorted, extract the element or elements being sorted upon, and sort the values, creating a stack or set of pointers to the goal records. The stack or set can then be processed under Global FOR (or with the TYPE command for stacks). A typical command sequence, using the SEQUENCE command, might be:
-> select conference -> for tree +> stack all -Stack: 53 PEOPLE +> sequence state name -Stack: 53 PEOPLE +> in active type +>
But if the CONFERENCE subfile already has a STATE index, the sorting of the goal records by state is always readily available, through the FOR INDEX command:
-> select conference +> for index state (sequence name) +> in active display all
This sequence of commands probably provides the same result (see the "Notes" section below for exceptions) and is more efficient, since now SPIRES does not have to sort all the goal records by state.
Let's consider this process in more detail. Suppose the CONFERENCE subfile has goal records and index records with the following structure:
Goal record STATE index record ID (key) STATE (key) NAME GOALREC.ID STATE
Each goal record has a key element called ID, a number, as well as a person's NAME and home STATE. In the STATE index, each index record has the name of a state as the key element, and a GOALREC.ID, which is the number of a goal record containing that state name. Suppose the following index records existed:
STATE = ALABAMA; GOALREC.ID = 5; GOALREC.ID = 2; STATE = CALIFORNIA; GOALREC.ID = 1; GOALREC.ID = 7; GOALREC.ID = 6; STATE = HAWAII; GOALREC.ID = 9;
Now you issue the following commands:
-> select conference +> for index state +> set format $report State ID Name +> display all June 26, 1984 Page 1 State ID Name -------------------- ---------------- ----------------- Alabama 5 Withaban, Joe Alabama 2 Tusca, Lou Sue California 1 Heric, Ome California 7 Santa, Barbara California 6 Ricer, Ronny Hawaii 9 Aloha, Roy +>
When the DISPLAY command is issued, SPIRES examines the first index record, retrieves the goal record keys, and begins retrieving the "named" goal records, one at a time.
Note that if you use an option on a record processing command that requests an individual record (DISPLAY FIRST, for example), you are requesting an individual goal record, not all the goal records in a single index record. In other words, DISPLAY FIRST would only display record 5 in the above example, because the DISPLAY command is requesting the processing of goal records, not index records. The index records are only used to provide an access path to the goal records.
You might imagine the index to be a giant stack of records, and the clusters of record pointers in a single index record (the two for ALABAMA, for instance) to be "sub-stacks". Using the SEQUENCE clause, the goal records in a sub-stack can be further sorted, if desired. For example, the table above can be further sorted by NAME:
-> for index state (sequence name) +> display all June 26, 1984 Page 1 State ID Name -------------------- ---------------- ----------------- Alabama 2 Tusca, Lou Sue Alabama 5 Withaban, Joe California 1 Heric, Ome California 6 Ricer, Ronny California 7 Santa, Barbara Hawaii 9 Aloha, Roy +>
The goal records are still arranged in order by state, but within each state they are arranged by name as well.
The syntax of the SEQUENCE clause is similar to that of the SEQUENCE command:
(SEQUENCE element-name [(D)|(X)|(DX)] element-name ...)
where "element-name" is the name of or a valid alias for a goal record element by which further sorting is to be done. The SEQUENCE clause must be placed in parentheses. The "(D)" option may be used to request that the values be sorted in descending order rather than ascending, for that particular element. The "(X)" option may be used to request that the external form rather than the internal form of the element be used for sorting (that is, the form in which the value is displayed, rather than the form in which it is stored). The two options may be combined as shown in the syntax: "(DX)". Other element names to be used in the sorting may follow, if desired. EXPLAIN SEQUENCE COMMAND, or see the SPIRES manual "Searching and Updating", section B.4.5 for more information about the SEQUENCE command.
Notes and Considerations:
Path processing in SPIRES is a facility that allows you to have multiple subfiles selected at once, or multiple "views", through multiple formats, of a single subfile. Preliminary documentation for this facility can be found in the SPIRES manual "Technical Notes", section 14.
The FOR PATH command is part of the path processing facility in SPIRES. The FOR PATH command is similar to the FOR INDEX command, except that the pointers to the desired goal records are not found in an index to the goal records but in another subfile. Also, the FOR PATH command may be considered an extension of the FOR INDEX command in that it can perform the same function as FOR INDEX in many cases but can also restrict the index records retrieved based on element criteria. That second purpose is discussed later in this section.
Suppose that you have two subfiles, COURSES and ROOMS:
subfile COURSES subfile ROOMS COURSE.NUMBER (key) ROOM.NUMBER (key) COURSE.NAME COURSE.NUMBER DAYS NUMBER.OF.SEATS TIME ROOM.NUMBER (etc.)
The goal record elements for the ROOMS subfile include a multiply occurring element called COURSE.NUMBER, which represents the numbers of courses taught in that room, the same type of value found as the goal record key for the COURSES subfile (BIO 239, for example).
Suppose you want a report on the classes taught, listed in order by room number; courses using the same room should be listed in order by day and time. The easiest way to solve the problem is with this group of commands:
SELECT COURSES FOR SUBFILE STACK ALL SEQUENCE ROOM.NUMBER DAYS TIME SET FORMAT $REPORT ROOM.NUMBER COURSE.NUMBER DAYS TIME TYPE
However, a more efficient method is to take advantage of the ROOMS subfile, which already has the room numbers sorted.
-> - First select the primary subfile. -> select courses -> - Next establish the path for the second subfile. -> through 1 select rooms -Path established: 1 -> - Choose the records in the 2nd subfile to be examined. -> through 1 for subfile -> - Establish Global FOR mode for goal records in primary. -> for path 1, pointer=course.number (sequence days time) +> set format $report room.number course.number days time +> in active display all
The Global FOR command THROUGH 1 FOR SUBFILE specifies which records in the ROOMS subfile are to be examined. The command THROUGH 1 FOR SUBFILE establishes which records in the ROOMS will be used as "access records" to the goal records in the COURSES subfile. In this case, FOR SUBFILE indicates that all the records in the ROOMS subfile will be used. The following command, "FOR PATH 1...", indicates that goal records in the COURSES subfile will be retrieved using the records available through "path 1", and that the element COURSE.NUMBER in those records should be used as the pointer to indicate which goal record in COURSES should be retrieved. In other words, the goal records in the ROOMS subfile, arranged in key order by room, are used as index records to goal records in the COURSES subfile, even though two different subfiles, perhaps not even in the same file, are involved.
Most of the Global FOR classes can be used in the "THROUGH pathname FOR class" command; the exceptions are RESULT, STACK, INDEX and PATH. (You can circumvent the prohibition of FOR RESULT and FOR STACK here by storing a result or stack with the STORE RESULT.name or STORE STACK.name command, and then using the stored result or stack as a set: THROUGH pathname FOR SET RESULT.name, for example.) The command may have a WHERE clause or SET SCAN commands associated with it in order to limit the "index records" processed or examined. [See 4, 6.2.]
The syntax of the FOR PATH command is similar to that of the FOR INDEX command:
FOR PATH pathname, POINTER = elemname [(SEQUENCE element-list)]...
... [WHERE criteria-clause]
where "pathname" is the name or number of a path established by a previous THROUGH... SELECT command and "elemname" is the name of the element in the path-selected subfile that contains the key values of records in the primary subfile. The SEQUENCE clause has the same form as it has on the FOR INDEX command and has the same uses. [See 2.14.] The WHERE clause is discussed in a later chapter. [See 4.]
Perhaps a more common use of the FOR PATH command is in situations where you could otherwise use FOR INDEX except that you want to restrict the index records accessed based on some criteria within them. (For example, you want to have a WHERE clause on the index records accessed rather than on the goal records.)
Suppose that both COURSES and ROOMS are subfiles in the same file:
subfile COURSES subfile ROOMS COURSE.NUMBER (key) ROOM.NUMBER (key) COURSE.NAME COURSE.NUMBER DAYS NUMBER.OF.SEATS TIME ROOM.NUMBER (etc.)
Suppose also that the goal records for ROOMS are index records for COURSES (i.e., we could issue the command sequence SELECT COURSES and FOR INDEX ROOMS). Here are two possible reports that could be produced:
1) -> select courses
-> through 1 select rooms
-> through 1 for subfile
-> through 1 set scan prefix TERMAN
-> for path 1, pointer=course.number (sequence days time)
+> set format $report room.number (heading = Room)
+> set format * + Course.number Days Time
+> display all
Feb. 3, 1984 Page 1
Room Course Days Time
-------------- --------------- ------------ -------------
TERMAN 011 ENG 119 M,W,F 0900-0950
TERMAN 011 ENG 223 M,W,F 1000-1150
TERMAN 113 PHY 475 T,H 0930-1045
(etc.)
2) -> select courses
-> through 1 select rooms
-> through 1 for subfile where number.of.seats > 100
-> for path 1, pointer=course.number (sequence days time)
+> set format $report room.number (heading = Room)
+> set format * + Course.number Days Time
+> display all
Feb. 3, 1984 Page 1
Room Course Days Time
-------------- --------------- ------------ -------------
CERAS 204 EDU 114 M,W,F 1100-1200
CERAS 210 EDU 103 M,W,F 1300-1400
(etc.)
In example 1, you request a list of all classes in the Terman building (i.e., room numbers prefixed by TERMAN) in order by room number. SPIRES only accesses those index records with keys beginning with the string TERMAN when it picks up pointers to the COURSE goal records. [See 6.2 for information on the SET SCAN commands.]
In example 2, you request a list of all classes held in rooms that can hold more than 100 people, arranged in order by room number. In both examples, you tell SPIRES to use criteria in the index records to determine which goal records to process.
Note that example 1 could probably have been done with a different method: a search command (FIND ROOM PREFIX TERMAN) and a SEQUENCE command. The second example could not have been done that way, however, since the COURSES goal record did not contain the needed criteria (the size of the room). For the first example, the method shown above is at least as efficient, and possibly somewhat more efficient, than the FIND and SEQUENCE method.
The same "Notes and Considerations" discussed under FOR INDEX apply to FOR PATH. [See 2.14.] The FOR PATH command is equivalent to the command FOR SUBFILE VIA PATH, meaning that all records processed under FOR PATH will be in their latest versions. [See 3.8.]
The FOR STORED command differs from the other Global FOR commands in that it is only available in SPIBILD. It is available for file owners to use when processing files. The syntax of the commmand is:
FOR STORED {RESULT.name|STACK.name} [WHERE clause]
where "RESULT" is a stored result and "STACK" is a stored stack.
For more detailed information on the use of this command, refer to the document "SPIRES File Management" or online, type EXPLAIN FOR STORED.
The FOR LOAD command processes records that have been copied into a load file using the GENERATE LOAD command. It is available for file owners and users with See access to the file containing the selected subfile.
The syntax of the commmand is:
FOR LOAD loadname [WHERE clause]
where "loadname" is the name of the ORVYL file containing the load data. If the load is stored under a different account, be sure to use the fully qualified form: "ORV.gg.uuu.loadname".
Using FOR LOAD is a handy way to see what records are in a load; once it is generated, the only other way to examine its contents is to load it back into the file or into another file. For that reason, the SHOW KEYS command is particularly useful with FOR LOAD.
For more detailed information on the GENERATE LOAD command, refer to the document "SPIRES File Management" or online, type EXPLAIN GENERATE LOAD.
The "FOR *" command is one of two commands that are special cases in Global FOR, the other being "FOR **". The "FOR *" command usually has a WHERE clause, and is used to change WHERE clauses in the middle of a Global FOR stream. It can only be issued when some other "FOR class" command is in effect.
The syntax of the command is:
FOR * [WHERE criteria-clause]
The WHERE clause is discussed later in this manual. [See 4.] No VIA clause is allowed on the "FOR *" command. [See 3.]
-> select drinks -> for tree where ingredients occurs > 6 +> show keys 2 47 138 +> for * where ingredients occurs > 4 +> show keys rest 141 144 +> show keys all 47 72 111 138 141 144 +>
In the example, you first ask to process the records in the tree with more than 6 occurrences of the INGREDIENTS element and then ask for the key values of the first two records that fit the WHERE clause criterion. Next, you issue the "FOR *" command, indicating a different WHERE clause to be used for subsequent record processing. However, your current position "within the class" remains the same: you will not start processing records at the beginning of the class again, but will continue from the last record processed while the previous WHERE clause was in effect. The example makes that point clear. Records 47 and 138 had already been processed when the "FOR *" command was issued, and so the SHOW KEYS REST command does not show them again. But when you ask to see the keys of all the records in the tree with more than four ingredients ("SHOW KEYS ALL") SPIRES begins processing again "from the top" and records 47 and 138 are included in the list. Thus, the "FOR *" command issued in the example is not equivalent to "FOR TREE WHERE INGREDIENTS OCCURS > 4", since that command would restart you at the top of the class. [See 5.1 for a discussion of your position within the class and the "current record pointer".]
If you issue a "FOR *" command without a WHERE clause, the current WHERE clause is no longer in effect, and processing continues from the same position.
Any SET SCAN commands in effect when a "FOR *" command is issued will remain in effect under the "FOR *" command unless you issue a CLEAR SCAN command. [See 6.2.]
The "FOR **" command simply reissues the "FOR class" command that is currently in effect. It can only be issued when you are already in Global FOR mode. Its syntax is simply:
FOR **
No VIA or WHERE clause is allowed on this command. [See 3, 4.]
-> for tree where ingredients occurs > 6 +> show keys all 47 138 141 +> for ** +> in active display 2 +>
In the example, "FOR **" is equal to "FOR TREE WHERE INGREDIENTS OCCURS > 6". Thus, it starts you over again without having to reissue the identical "FOR class" command. In most cases, using the FIRST or ALL options on the subsequent Global FOR commands will probably be as effective as the "FOR **" command. [See 5.1.] For instance:
+> for ** +> in active display first +> in active display 2 +> in active continue display next
If the two commands on the right are substituted for the two commands on the left in the previous example, the result will be about the same. (The difference is that the resulting active file from the commands on the left will include a line of four asterisks, "****", before each record and a semicolon, ";", after each one, which will not happen from the single-record processing commands on the right.)
Once you have left Global FOR mode, either because you issued an ENDFOR command or because you were automatically ejected, you cannot issue the "FOR **" command. Any SET SCAN commands in effect when a "FOR **" command is issued will remain in effect under the "FOR **" command, unless you issue a CLEAR SCAN command. [See 6.2.] Alternatively, you can reissue the original "FOR class" command, which will clear any "scans" in effect.
Suppose that you want to see the original tree copies of all the updated records in the deferred queue. How would you do it? The FOR UPDATES command accesses the latest copies; the FOR TREE command would access the tree copies, but would give you all the records in the tree rather than just the updated ones. The solution requires a VIA clause on a "FOR class" command.
The VIA clause on the "FOR class" command allows you to specify that only the records in the Global FOR class whose keys (or pointers) are determined by the access class of the VIA clause are to be processed by subsequent Global FOR commands. The records in the Global FOR class are then processed in the order of the keys in the access class. In other words, the "access-class" in the VIA clause determines the keys of the records to be processed as well as the order in which they should be processed; the class in the "FOR class" part of the command determines where SPIRES should get each record. (The "class" in a "FOR class" command not having a VIA clause specifies all of that at once: the keys of the records to be processed, the order and the source.) Of course, even with a VIA clause, the "FOR class" command does not cause SPIRES to start processing records (looking for records in the "class" that are also in the "access class"). That activity does not begin until a record-processing command is issued. [See 5.]
Only some access classes discussed in the previous chapter can be used in a VIA clause (plus TREE in a special case), and only seven retrieval classes can be the Global FOR class when a VIA clause is used. [See 2.]
The syntax of the "FOR class" command using a VIA clause is:
FOR class VIA access-class [WHERE criteria-clause]
where "access-class" can be STACK, RESULT, "SET setname", TRANSACTIONS, RESIDUAL DATA, "INDEX index-name", "PATH pathname" or TREE, and where "class" can be TREE, SUBFILE, DEFQ, ADDS, UPDATES, REMOVES, or TRANSACTIONS.
There is also a separate set of VIA "access-classes" that can be used with the FOR TRANSACTIONS command. [See 3.9.]
There are over fifty combinations of "class" and "access class", not all of which are useful or meaningful. Here are some examples of "FOR class" commands with VIA clauses:
FOR TREE VIA STACK FOR SUBFILE VIA STACK FOR UPDATES VIA RESULT
For instance, FOR TREE VIA STACK, as discussed at the beginning, indicates that we want to begin processing the tree copy of records in the current stack. (Of course, strictly speaking, there are no records in a stack or result or set -- only keys or pointers. However, because accessing a result, stack or set in Global FOR usually leads you to records, we can take the liberty of referring to "the records in a result", for instance.) FOR SUBFILE VIA STACK indicates that we want to process the latest copy of each record in the stack. (FOR SUBFILE VIA STACK is equivalent to FOR STACK, so it is seldom issued in this longer form.) FOR UPDATES VIA RESULT means that we want to process the latest copy of all records being updated that are found by the search result.
Here is a chart showing the allowed combinations of VIA clauses:
+-----------+------------------------------------------------------------+ | | VIA access-class | | FOR class | RESULT | STA | SET | TRANS | RES DAT | INDEX | PATH | TREE | |-----------+--------+-----+-----+-------+---------+-------+------+------| | TREE | | | | X | D | | | X | |-----------+--------+-----+-----+-------+---------+-------+------+------| | SUBFILE | D | D | D | X | | D | D | | |-----------+--------+-----+-----+-------+---------+-------+------+------| | DEFQ | | | | D | X | | | X | |-----------+--------+-----+-----+-------+---------+-------+------+------| | ADDS | | | | | X | | | X | |-----------+--------+-----+-----+-------+---------+-------+------+------| | UPDATES | | | | | X | | | X | |-----------+--------+-----+-----+-------+---------+-------+------+------| | REMOVES | | | | | X | | | X | +-----------+--------+-----+-----+-------+---------+-------+------+------+
The positions marked with an "X" indicate combinations that are not allowed. Those marked with a "D" indicate the default combination when only the class that would be used in the VIA clause is used in a "FOR class" command (e.g., FOR SUBFILE VIA RESULT is the actual result when you issue the FOR RESULT command). The positions without a mark indicate combinations that are allowed.
The following chart shows the special set of access classes that can be used with the FOR TRANSACTIONS command:
+------------------+----------------------------------------+ | | VIA | | FOR TRANSACTIONS | DEFQ ADDS UPDATES REMOVES | +------------------+----------------------------------------+
The sections in this chapter will describe each access class as it is used in a VIA clause. Further information about the classes can be found in the previous chapter. [See 2.]
The VIA RESULT clause is used to specify that the records in the Global FOR class that are also in the current search result are to be processed by subsequent Global FOR commands. The records will be processed in the order in which their keys or pointers occur in the search result. [See 2.9.] If you change the search result during processing, the next Global FOR command will begin processing records in the new result from the top.
Below are the six possible "FOR class" commands using the VIA RESULT clause:
This is equivalent to FOR RESULT. [See 2.9.] When processing records under this command, SPIRES examines the deferred queue. The latest copy of an updated record is processed; records being removed are not processed at all.
This command is used to examine the tree versions of all the records in the search result. Thus, any records removed or updated during the day would still be processed in their tree versions. Unless the indexes are immediate indexes, remember that the indexes reflect the TREE rather than the SUBFILE copies of records, since records but not indexes may have been updated since the file was last processed.
This command will access only the records in the search result that have been added since the file was last processed. Note that this command will only yield a meaningful result if the indexes used to create the result are immediate indexes.
This command will access only the records in the search result that are being updated. The latest copy of each will be processed under this command.
Because the class DEFQ combines ADDS and UPDATES, this command is equivalent to FOR UPDATES VIA RESULT, described above.
This command is useful when you need to determine whether any records in the current search result are being removed, and if so, which ones. Remember that only a few Global FOR commands can be issued under a FOR REMOVES command. [See 2.7.] Suppose for instance that you had removed several hundred records from a subfile during the day and you needed to know whether any of them had been updated since 1979:
-> find date-updated after 1979 -Result: 196 PEOPLE -> for removes via result +> stack all -Stack: 5 PEOPLE
You could then use a FOR TREE VIA STACK command to see which records they were. [See 3.2.]
To specify that the records in a Global FOR class whose keys or pointers are in the current stack are to be processed, use the VIA STACK clause on a "FOR class" command. You can then process the records in the order in which they are stacked. The stack can be changed during your processing, but subsequent Global FOR commands will start processing from the start of the new stack rather than continuing with the old one, unless you alter the stack by issuing the STACK command. [See 5.8 for a discussion of this exception.] A stored stack can be processed using the FOR SET command or by using the VIA SET clause on a "FOR class" command.
Below are the six possible "FOR class" commands using the VIA STACK clause:
This command is equivalent to FOR STACK. [See 2.10.] The latest versions of records will be processed, meaning that records being removed will not be processed at all.
This command prepares SPIRES for processing copies of records from the tree that are in the stack. It is often used when you are trying to get the original tree copies of records being removed or updated:
-> for updates +> stack all -Stack: 5 PEOPLE +> for removes +> stack all -Stack: 13 PEOPLE +> for tree via stack +> in active display all +>
If any records in the stack have been added since the file was last processed, this command could be used to retrieve only them. If the stack was created by sequencing a search result, this command would not be very useful. [See 3.1, the FOR ADDS VIA RESULT command.]
This command would be useful if you wanted to process only the records in a stack that had been updated.
This command accesses records being added or updated that are in the current stack. It should not be confused with the FOR SUBFILE VIA STACK command (FOR STACK) which accesses the latest copy of all records in the subfile, including adds and updates, that are also in the stack. FOR DEFQ VIA STACK accesses only added and updated records in the stack.
Similar to FOR REMOVES VIA RESULT, this command is useful when you need to determine whether any records in the current stack are being removed.
Once you have created a set of records, you process those records with either the "FOR SET setname" command or a "FOR class" command that has a "VIA SET setname" clause, where "setname" is the name you chose for the set when you issued the DEFINE SET command. (Remember that the set can also be a stored stack or result.) [See 2.11.] The records in the Global FOR class that are also in the set can then be processed in the order in which their keys are arranged in the set.
The "FOR class VIA SET setname" commands are discussed in detail below:
This command works identically to the "FOR SET setname" command, providing access to the latest copies of the records specified in the set. [See 2.12.]
Using this command, you will be able to access tree copies of all records in the set. As with all FOR TREE commands, this command allows you to access records slightly more efficiently than a FOR SUBFILE command, since processing under FOR TREE does not involve checking the deferred queue for the latest version of each record. Hence, FOR TREE would be preferable to FOR SUBFILE (see below) if the file had recently been processed and there were few or no updated or removed records in the deferred queue.
The remaining four possibilities, FOR ADDS, FOR UPDATES, FOR DEFQ, and FOR REMOVES, all with "VIA SET setname" clauses are similar to their counterparts with VIA RESULT or VIA STACK clauses. [See 3.1, 3.2.] The difference is that it is the records in the named set, rather than those in the current result or stack, that are accessed in the class.
So far, each of the access classes in a VIA clause has specified a subset of records in the Global FOR class that you want to process. The VIA TRANSACTIONS clause, however, has a different effect on "FOR class" processing. A "FOR class VIA TRANSACTIONS" command specifies that subsequent commands will process the specified copies of the records in the transactions list that are in the Global FOR class. Details and examples given below for each of the commands should make this clear. Note too that the information discussed in the section on the FOR TRANSACTIONS command is relevant here. [See 2.8.]
This command is equivalent to FOR TRANSACTIONS. It accesses all the versions in the deferred queue of records that have been added or updated that day, even if they were later dequeued. Thus, if a record was added and then updated later, both copies could be accessed by this command. The copies are accessed in the order in which they were added or updated, oldest to newest. You should read about FOR TRANSACTIONS for more details. A similar command is FOR TRANSACTIONS VIA DEFQ. [See 3.9.]
This command allows you to access all copies of records added to the subfile during the day, even if they have since been dequeued. If a record is added and updated later that day, it is still considered an "add" by SPIRES; both copies can be accessed under the FOR ADDS VIA TRANSACTIONS command and neither copy with the FOR UPDATES VIA TRANSACTIONS command (discussed below). Suppose for instance that today record 17 was added, record 18 was added, record 17 was dequeued, record 18 was updated, and finally record 17 was added again:
-> for adds +> show keys all 17 18 +> for adds via transactions +> show keys all 17 18 18 17 +>
The FOR ADDS command accesses only the latest copy of the records being added, meaning that any dequeued adds will not be processed. The FOR ADDS VIA TRANSACTIONS command shown above accesses the original added versions of records 17 and 18, then the updated copy of 18 and then the newly added copy of 17. You may also find the FOR TRANSACTIONS VIA ADDS command useful. [See 3.9.]
Similar to FOR ADDS VIA TRANSACTIONS, this command allows you to access all deferred queue copies of records in the tree that have been updated during the day, even if they have since been dequeued. As noted before, FOR UPDATES does not include copies of added records that were updated later the same day. Under FOR UPDATES VIA TRANSACTIONS, records will be processed by Global FOR commands in the order in which they were updated. See also FOR TRANSACTIONS VIA UPDATES. [See 3.9.]
Using this command you can determine which records have been removed during the day, including those which have been dequeued:
-> for removes +> show keys all 5 17 +> for removes via transactions +> show keys all 17 25 25 5 +>
In the above example, the SHOW KEYS ALL command issued under FOR REMOVES shows that records 5 and 17 are being removed. However, the SHOW KEYS ALL command issued after FOR REMOVES VIA TRANSACTIONS shows that record 17 was removed first, then 25, then 25 again (that is, it was probably dequeued and later removed again) and then 5, with record 25 dequeued again at some point. Thus, the records are processed in the order in which the original transactions occurred. The FOR TRANSACTIONS VIA REMOVES command may also be useful. [See 3.9.]
Like the FOR RESIDUAL DATA command, the VIA RESIDUAL DATA clause is useful primarily to file owners. Only two "FOR class" commands have any meaning with this clause; the other four are not legal with it. The two that are meaningful are FOR TREE and FOR SUBFILE.
This is the equal of FOR RESIDUAL DATA -- both commands have the same effect. [See 2.11.] This type of processing actually does not involve the tree but since it does retrieve "tree copies" (the "original" copy in the subfile before any of today's updating activity is taken into account), the command syntax reflects the general meaning rather than the strict one.
This command gives you access to the latest copy of each of the records in the residual data set. If the record has been updated today, the most recent version will be processed; if the record has been removed today, the record will not be processed. Otherwise, the "tree copy" will be processed. No added records are processed since they are not in the residual data set.
Remember that the same record could be processed twice if it occurs twice in the residual data set; for that reason, these commands are not recommended for general use. [See 2.11.]
The VIA TREE clause is allowed only on a FOR SUBFILE command.
FOR SUBFILE VIA TREE is similar to FOR TREE in that you can sequentially process all the records in the tree under both commands. However, FOR SUBFILE VIA TREE requests that, for every record in the tree, SPIRES examine the deferred queue to see if the record has been updated or removed. Then the most recent version of the record should be used in processing. Hence, if a record has not been updated since the file was last processed, it will come from the tree; if it has been updated, it will come from the deferred queue; if it has been added or removed, it will not be processed at all.
This command replaces (and is equivalent to) the command FOR GOAL, which is still allowed. If FOR GOAL is issued with a VIA clause, such as FOR GOAL VIA ADDS, it is equivalent to FOR SUBFILE with the same VIA clause.
Taking advantage of the record sequencing inherent to an index, you can process the goal records in a particular order without having to create a set or stack first. Of course, the element by which you want the records sorted must be indexed. If this is the case, you can use the FOR INDEX command or a "FOR class" command with the "VIA INDEX index-name" clause appended. The records in the Global FOR class that are also indexed in the named index can then be processed in the order in which they are indexed.
The VIA INDEX clause may have a SEQUENCE clause to indicate further sequencing of the goal records having the same indexed value:
FOR class VIA INDEX index-name [(SEQUENCE element-list)]
where the SEQUENCE clause, which must be enclosed in parentheses, has the same syntax as the SEQUENCE command. It is discussed in the section on the FOR INDEX command. [See 2.14.]
The "FOR class VIA INDEX index-name" commands are discussed below:
This command works identically to the "FOR INDEX index-name" command, providing access to the latest copies of records specified in the index.
This command allows you to access tree copies of records in the index. It is slightly more efficient than FOR INDEX, and because the tree copies more accurately reflect the contents of the indexes than the "subfile" copies might, this form may be preferable in many cases. (FOR INDEX and the VIA INDEX clause do not examine the deferred queue of the index record.)
The other four possibilities, FOR ADDS, FOR UPDATES, FOR DEFQ and FOR REMOVES, used with "VIA INDEX index-name" clauses, all work similarly to their counterparts with VIA STACK or VIA RESULT. [See 3.1, 3.2.]
If the goal records of another subfile can be considered as index records to a subfile, path processing techniques in conjunction with Global FOR processing can be used to process the subfile's records via the second subfile. This concept was discussed in detail in the section on the FOR PATH command. [See 2.15.]
The FOR PATH command is a shortcut for FOR SUBFILE VIA PATH. The "VIA PATH pathname" clause can be added to a "FOR class" command to indicate that the records in the Global FOR class that are specified by the named path will be processed by subsequent Global FOR commands.
The VIA PATH clause may have a SEQUENCE clause to indicate further sequencing of the goal records:
FOR class VIA PATH pathname, POINTER=elemname [(SEQUENCE element-list)]
where the SEQUENCE clause, which must be enclosed in parentheses, has the same syntax as the SEQUENCE command. The "elemname" is the name of the element in the path-selected subfile that is the pointer to the goal records in the primary subfile. [See 2.15 for examples.] It is discussed in the section on the FOR INDEX command. The "FOR class VIA PATH pathname" commands are described briefly below:
As mentioned above, this is equivalent to the FOR PATH command, discussed earlier in this manual. It provides access to the latest copies of records specified in the set.
Using this command, you can access the tree copies of all goal records in the primary subfile that have pointers provided by the path-selected subfile.
The other four possible classes, ADDS, UPDATES, DEFQ and REMOVES, work similarly with a VIA PATH clause to the way the work with a VIA STACK or VIA RESULT clause. [See 3.1, 3.2.]
The FOR TRANSACTIONS command accesses different versions of added and updated records since the file was last processed. If a record has been added, updated, or removed, the FOR TRANSACTIONS VIA class command is another way to keep track of the multiple copies of records in the DEFQ.
The FOR TRANSACTIONS command allows a subset of access classes that are only allowed with the FOR TRANSACTIONS command. These are: VIA DEFQ, VIA ADDS, VIA UPDATES, and VIA REMOVES.
This command differs from the simple FOR TRANSACTIONS command in a number of ways.
1) The FOR TRANSACTIONS command presents the records in the order in which they were added or updated. However, FOR TRANSACTIONS VIA DEFQ presents each record in sequence by record key. For example:
-> for transactions +> show keys all 25 24 25 17 19 +> for transactions via defq +> show keys all 17 19 24 25 25 +>
2) The FOR TRANSACTIONS VIA DEFQ command includes the TREE copy of each record that is accessed -- that is, the original copy of the record in the database before any changes were made.
3) In the FOR TRANSACTIONS command, records are presented that never were added to the file (e.g. a DECOMITted transaction). These do not show up in the FOR TRANSACTIONS VIA DEFQ command.
For more detail about the transactions, it is recommended that the file owner use the SHOW SUBFILE TRANSACTIONS command.
This command allows you to access the transactions for the records that have been ADDed to the subfile in key sequence. If a record is added and updated that day, it is still considered an "add" by SPIRES; both copies can be accessed under the FOR TRANSACTIONS VIA ADDS (but not if the record has since been removed)
FOR TRANSACTIONS VIA ADDS differs from FOR ADDS VIA TRANSACTIONS in the following ways:
1) The records in FOR TRANSACTIONS VIA ADDS are presented in key sequence.
2) If the record has been added and then removed, it is not included in the FOR TRANSACTIONS VIA ADDS (but it is listed under FOR ADDS VIA TRANSACTIONS).
-> for adds via transactions +> sho keys all 6 7 8 9 10 9 9 +> for transactions via adds +> show keys all 6 7 8 9 9 9 10 ->
In the above example, FOR ADDS VIA TRANSACTIONS shows that record 9 was added to the database, then record 10, and then record 9 was updated two more times -- the keys are presented in the order that the adds and updates were made. On the other hand, FOR TRANSACTIONS VIA ADDS processes the records in key order. [See 3.4.]
This command allows you to access the transactions for the records that have been UPDATEd in the subfile in the key sequence. If a record is added and updated that day, it is still considered an "add" by SPIRES; both copies can be accessed under the FOR TRANSACTIONS VIA ADDS (but not if the record has been removed) FOR TRANSACTIONS VIA UPDATES does not include copies of added records that were updated later the same day. [See 3.4.]
This command allows you to access the transactions for the records that have been REMOVEd from the subfile in key sequence. However, if a record has been added and removed that day, it can only be accessed through the FOR TRANSACTIONS or FOR TRANSACTIONS VIA DEFQ commands.
So far in this manual, Global FOR has been used either to retrieve entire classes of records or else to retrieve subsets of records from one class that are also contained in another "access" class. For instance, using FOR ADDS, you can access all the added records in a subfile. But sometimes you need more focused criteria for record retrieval than a class of records (or intersection of classes) can provide.
For example, suppose you are looking for an added record in the DRINKS subfile and you do not remember the key of the record, though you do remember that the record has "gin" as one of the values for the element CONSTITUENT. If only a few records have been added to DRINKS since the file was last processed, you can easily retrieve the record you want by examining one record after another in the ADDS class:
-> select drinks -> for adds +> display
But if quite a few records have been added since the file was processed, the method above will be time-consuming, since you will have to look at all the added records, whether or not they have a CONSTITUTENT of gin.
If you include a WHERE clause on the FOR ADDS command, you can have SPIRES do this investigative work for you:
-> select drinks -> for adds where constituent = gin +> display
After you have issued a Global FOR command with a WHERE clause, your subsequent Global FOR commands will only process the subset of records that have passed this WHERE filtering. Thus, in the second example above, DISPLAY commands will only display the subset of added records with a CONSTITUENT occurrence of gin. (Note that SPIRES generally still "examines" the entire class, even though it only "processes" the filtered subset.) In general, a WHERE clause on a "FOR class" command specifies criteria that records in the class must meet in order to be processed by subsequent Global FOR commands.
A WHERE clause sometimes makes Global FOR processing of records more expensive for the following reason. When you process records under a "FOR class" command without a WHERE clause, SPIRES knows instantly which records are to be processed (i.e., all the records in the class). But when a WHERE clause is in effect, SPIRES usually must examine each record individually to see which records fit the criteria and should be processed. Because the WHERE clause causes more work for SPIRES, it costs more to use than an unadorned "FOR class" command does. On the other hand, it is certainly more efficient for SPIRES to examine the records than for you to do so. In addition, you can often reduce expenses by using a smaller class (such as a result instead of the tree), or a VIA clause, or a SET SCAN command.
The example above shows the simplest WHERE clause available, but your WHERE clause can be both more complex and more precise. For instance, the WHERE clause can name more than one criteria, thereby specifying that records retrieved must satisfy all of the conditions you name (or must satisfy any -- or even none -- of the conditions you name). Or your criteria clause can use the OCCURS option to request records that merely have a value for an element, regardless of what that value is. With the "inter-element relations" capability, your criteria clause might request only records in which one element equals (or is less than or greater than) another element in the record. [See 4.5.]
The WHERE clause offers other capabilities besides these, and most of them will be discussed in detail within this chapter. The upcoming section discusses the syntax and power of the "simple" WHERE clause, in which you specify only one criterion clause. [See 4.1.] The section following that one discusses the "compound" WHERE clause, which lets you specify multiple criteria for greater precision. [See 4.2.] The final sections discuss more advanced WHERE clause capabilities for specialized uses, such as binding element retrieval to a structural path. [See 4.4.]
The ALSO command is another mechanism, in addition to Global FOR commands, for searching on unindexed elements. The ALSO command only operates on a preceding search result; for example:
-> find ingredient rum -Result: 34 BEVERAGES -> also contributor john smith -Result: 16 BEVERAGES
The ALSO command takes an existing result or stack and creates another result or stack that satisfy the conditions named in the criteria clause. The ALSO command can use WHERE-clause terms, such as OCCURS, LENGTH, "same structure" processing, and inter-element relations. Therefore, keep in mind that the discussion in the rest of this chapter that pertains to WHERE clauses also applies to ALSO criteria clauses.
A WHERE clause has two significant limits that are worth knowing if you work with many or long values: it can have only 256 bytes of values and only 30 expressions.
So, for example, if you have this command:
FOR SET WHERE SUBJECT = RAIN OR SNOW OR SLEET OR TITLE HAIL CAESAR
The total number of bytes is the total of the lengths of the strings RAIN, SNOW, SLEET and HAIL CAESAR: 24 bytes. (Note: The total is based on the lengths of the converted, i.e., internal values of the strings.)
The example has 4 expressions:
SUBJECT = RAIN SUBJECT = SNOW SUBJECT = SEX TITLE = HAIL CAESAR
If your WHERE clause exceeds either of these limits, SPIRES gives you the error message "WHERE clause overflow".
A simple WHERE clause, when used on a "FOR class" command, has the following syntax:
FOR class WHERE [NOT] criteria-clause
The simple "criteria-clause" has the following syntax:
element [OCCURS|LENGTH] relational-operator value
Here "element" is the name (or a valid alias) of the element to be examined by SPIRES, "relational-operator" is one of the standard SPIRES relational operators listed below, and "value" is the value to be compared with the element value or values. OCCURS and LENGTH are discussed in detail later in this section. A dynamic element name can be specified for "element". [See Chapter 20 of "SPIRES Technical Notes" for an explanation of dynamic elements.]
When you issue a Global FOR command with a WHERE clause, SPIRES will process all records in the class that fit the criteria specified. If a WHERE clause specifies that an element must match a particular criterion, any record with an occurrence of that element matching the criterion will be processed, even though other occurrences of the same element within the record might not match the criterion at all. (You can use a filter to mask extraneous element occurrences. See the description of filters in the manual "SPIRES Technical Notes"; see also Section 4.4 later in this chapter.)
In comparing criteria clause values to stored element values in a record, SPIRES ignores the case (upper or lower) of both values, so you do not have to worry about case in your criteria clause. However, since SPIRES does process the criterion value through any INPROCs (processing rules for record input) that have been coded for the element, the value you specify in a WHERE clause must generally be valid as an element value. This topic is covered in more detail in Section 4.3.
All of the standard SPIRES relational operators, in the four categories of equality, range, inequality and content, are available in WHERE clauses and are listed below. If you do not use a relational operator, SPIRES will assume you want the equality operator. [See "SPIRES Searching and Updating", section B.3.2.3 for details on relational operators.]
equality range inequality content
-------- ------------- ---------- -------
= BEFORE ~= LIKE
(blank) AFTER > PREFIX
BETWEEN...AND >= SUFFIX
FROM...TO < WORD
<= STRING
HAVING
MASK
WITH
When a WHERE clause is in effect, all relational operators are equally efficient, since SPIRES will examine the entire element value under Global FOR in any case. Thus, for example, even operators like WORD or STRING, which can be quite inefficient in an indexed search, become just as efficient as the equality operator in a WHERE clause.
Here are some examples of "FOR class" commands with simple WHERE clauses:
FOR ADDS WHERE CONTRIBUTOR = GQ.JAN FOR DEFQ WHERE CONTRIBUTOR LIKE G_.JAN FOR RESULT WHERE DATE-ADDED BEFORE 1985 FOR TREE WHERE COMMENTS STRING FOOD FOR TREE VIA RESULT WHERE CHILDREN < 3 (a) FOR TREE VIA RESULT WHERE CHILDREN OCCURS < 3 (b)
In the example marked (a) above, it is important to note that the command is asking to examine values of the CHILDREN element, not the number of occurrences of the element. The command would only be meaningful if the CHILDREN element contained integer values (e.g., for number of children). If the element has textual values such as "John", "Jerry", "Carolyn" and "Ellen", the record would still be processed, but the processing would convey false information, since in sorting, alphabetic characters are always considered "less than" numerical ones. Command (b), on the other hand, uses the OCCURS option, discussed further below, to process by the number of occurrences of a textual element.
You can use a WHERE clause to access records that have a particular number of occurrences of an element or that have occurrences of a particular length. Using the OCCURS option, for example, you can specify that you want to process records that do not have any occurrences of a particular element or structure:
FOR TREE WHERE CONTRIBUTOR OCCURS = 0
Using the LENGTH option, you can ask to process records with at least one occurrence of a particular element that is a particular length:
FOR TREE WHERE CONTRIBUTOR LENGTH = 30
LENGTH refers to the number of bytes of storage used by the element value in internal storage within the file. Because elements with numerical values are often converted from string values into some other type (dates are stored internally as a four-byte HEX value, for instance), the LENGTH operator is generally most useful with textual data, such as an address or a comment.
In WHERE clauses using either LENGTH or OCCURS, a value must be supplied. Thus, FOR UPDATES WHERE PHONE OCCURS is not valid; the equivalent command "FOR UPDATES WHERE PHONE OCCURS > 0" is valid. Remember that if no relational operator is used, the equality operator is assumed. If any occurrence of the element being examined fits the criterion specified, that record will be processed.
When you use the OCCURS option for a dynamic element, the number of occurrences of the primary element in the dynamic element definition controls the number of occurrences of the dynamic element. [See Chapter 20 of "SPIRES Technical Notes" for an explanation of dynamic elements.]
The LENGTH and OCCURS options can also be used with elements that are structures, as described further below.
The NOT option, when used in a simple WHERE clause, tells SPIRES that you want to process records only when the stated criterion does not occur as any of the occurrences of the named element. For example, a record with a ZIP-CODE value of 94305 would be processed under the command "FOR TREE WHERE ZIP-CODE 94305" but would not be processed under the command "FOR TREE WHERE NOT ZIP-CODE 94305". The set of records processed under one of these commands would exactly complement the set processed under the other command.
Note that the NOT option operates differently from the inequality operator (~=) with records having multiple occurrences of the named element. For example suppose a record contains two addresses and hence two zip-codes, 94305 and 94010. The record will be processed under the command "FOR TREE WHERE ZIP-CODE 94305" because, after all, there is one occurrence of the ZIP-CODE element that did fit the specified criterion; but it will not be processed under the second command, "FOR TREE WHERE NOT ZIP-CODE 94305", even though one of the occurrences is not 94305. In other words, the NOT option will only process records in which there were NO occurrences whatsoever of the value named.
On the other hand, after a command using the inequality operator "~=" instead of the NOT option the record containing both zip-codes would be processed, because it does have one occurrence of ZIP-CODE that is not equal to 94305.
Suppose a subfile has the following three records:
NAME = Acme; NAME = Brentwood; NAME = Co-op; ZIP = 94010; ZIP = 94010; ZIP = 94305; ZIP = 94305; ZIP = 94123; ZIP = 94305;
The records would be processed under "FOR class" commands as follows:
FOR class WHERE ZIP = 94305 FOR class WHERE ZIP ~= 94305 Acme, Co-op Acme, Brentwood FOR class WHERE NOT ZIP = 94305 FOR class WHERE NOT ZIP ~= 94305 Brentwood Co-op
Although simple elements are most often named in WHERE clauses, structures can also sometimes be used. In the examples below, PHONE is a structure consisting of the elements AREA-CODE and LOCAL-NUMBER.
-> for result where phone occurs > 1 +>
With that command you could begin processing records in a search result that had more than one occurrence of the PHONE structure.
Structures can also be used in WHERE clauses with the LENGTH operator, but not as easily. Because the stored value of a structure may contain additional bytes of information (such as the length and number of occurrences of each element in the structure) these bytes become part of the total length of the structure. Though you may be able to determine exactly how many extra bytes will be added [See "SPIRES File Definition", section B.6.2.] it is easier to use the inequality operators such as ">" or "<=" than to try working with exact lengths.
There are cases where the "value" of a structure (when that value has been assembled from the values of separate elements within the structure) can be used in a WHERE clause just as though it were a conventional, single element value. In the example below, once again the value for PHONE is in fact built out of values for AREA-CODE and LOCAL-NUMBER:
-> for result where phone 415-497-4420 +>
Such a construction is only allowed when the structure is processed by $STRUC or $STRUC.IN as an INPROC. The general rule is that, if the structure can be entered into the record as a single value (which is then broken up into structural elements by SPIRES), such a value can be used in a WHERE clause.
Sometimes you might wish to specify more than one criterion in a WHERE clause. For instance, suppose you are searching for records with an "only child" named John -- you don't want to retrieve any records where John is just one of several children. To satisfy multiple conditions, you issue a compound WHERE clause -- that is, a WHERE clause that combines two or more criteria clauses, using one of the three logical operators, AND, AND NOT and OR:
FOR TREE WHERE CHILDREN = JOHN AND CHILDREN OCCURS = 1
There are only two relatively broad limits to compound WHERE clauses: 1) only 30 relational operators maximum are allowed; and 2) the entire "FOR class" command cannot be longer than 235 characters. You can circumvent the second limit either by using short element aliases and command abbreviations, or by assigning parts of the WHERE clause to SPIRES variables for substitution when the command is issued. [For more on variables, see the manual "SPIRES Protocols".]
The complete syntax of a criteria clause is:
element [OCCURS|LENGTH] relational-operator value
[{AND|[AND] NOT|OR} criteria-clause]
where "criteria-clause" is another criteria clause with the same syntax as the first.
In a simple WHERE clause, the "element" is not optional. However, if there are two or more criteria clauses, the name is optional in the second or later clauses -- the last named element will be the default. Generally, this shortcut should only be used with the logical operator OR, because the presence or absence of the element name in the second clause is significant to the meaning of the clause when used with the AND or AND NOT operator. [See 4.2.4.] These two commands are equivalent:
FOR TREE WHERE ZIP 94305 OR 94306 FOR TREE WHERE ZIP 94305 OR ZIP 94306
(It is usually more efficient to repeat the element name, particularly if the goal record has phantom structures.)
The same shortcut is not allowed for relational operators, however. In the first command below, the PREFIX operator is only in effect for the first criteria clause. The default equality operator is assumed to apply to the second clause. Thus, these two commands are equivalent:
FOR TREE WHERE PHONE PREFIX 497 OR 324 FOR TREE WHERE PHONE PREFIX 497 OR PHONE = 324
To find all phone numbers with a prefix of 497 or 324, the PREFIX operator should be repeated, as in this command:
FOR TREE WHERE PHONE PREFIX 497 OR PREFIX 324
Here are several other examples of "FOR class" commands using compound WHERE clauses:
FOR SUBFILE WHERE NAME OCCURS = 0 AND NOT STATUS = OPEN FOR TRANSACTIONS WHERE START-DATE FROM 1976 TO 1978 OR FROM 1981 TO 1984 FOR * WHERE ADDRESS OCCURS > 2 OR ADDRESS LENGTH > 30
Though unnecessary, the element name ADDRESS is repeated in the last example to avoid confusion. You would only need to repeat it if there were an element in the subfile named LENGTH that you did not want used.
Parentheses can sometimes help clarify a compound WHERE clause:
-> for tree where date-added 1984 and (date-updated 1984 or 1985)
In this example, you are requesting records added in 1984 that were updated in 1984 or 1985. Omitting parentheses would create an entirely different request:
-> for tree where date-added 1984 and date-updated 1984 or 1985
Here you would be requesting records added in 1984 that were updated in 1984 or any records updated in 1985, even if they were added in some year other than 1984. The rules about using parentheses in a WHERE clause are the same as those for using them in a search request. [See "SPIRES Searching and Updating", section B.3.4.2.]
In a compound WHERE clause, the NOT option will only apply to the first criterion:
FOR TREE WHERE NOT ZIP-CODE 94025 AND STATE CALIFORNIA
That command wi