Collections

Collections

Definition and Style

The style of the TQL is first to isolate the data subset of interest, then to operate on it. By operate, we mean sort it, print it, correlate with it, change it, etc., just about anything you want to do with it.

To obtain an isolation there are five isolation spaces, which we call collections. Collection names are A, B, C, D, and E.

A collection contains the result of a query. For example:

                find a where city = seattle and age > 33

will isolate into collection A all records from any table in the database for which the selection criteria "city equals seattle and age greater than 33" is true. Subsequent commands, such as SORT, READ, PRINT, or CHANGE refer to collection A and thus deal only with the subset of interest rather than having to plow through the whole database.

The convenience, response, and capabilities of this style will make themselves felt when you are operating on a couple of hundred data records selected from a pile of millions of data records.

Structure of a Collection

A collection always represents the whole Database. For example, a collection representing a database composed of 10 Tables will have 10 portions to it. Each Table's portion of a collection contains the isolation achieved from that Table.

The TALLY command will always show you the current status of a collection.

Isolation-type commands (FIND, MATCH, PICK, etc.) put subset representations into a collection. Arranging-type commands (SORT, RELATE, etc.) order and/or combine and order the subsets represented in the collection. Display-type commands (REPORT, etc.) display the results of your isolating and arranging.

All of these commands can operate on the collection as a whole, or they can be focused to operate on a particular Table's portion of a collection. For example:

                sort a by cust#                                     -Sorts all records represented in all of A

                sort a customers by cust# -Sorts just CUSTOMERS records in A

This gives rise to "the most common problem encountered by 1st time users ."

Examples of Collections in Use

This section presents several examples of collections in use. The CLEAR, PICK, FIND, MATCH, FILL, and RAWPRINT commands are illustrated.

You will see that once a collection exists, it can be used as a term or a factor in subsequent query expressions. You will see some variety in syntax that can make up query expressions, including the fact that even though we talk about the FIND command, the word FIND is optional in the syntax.

To begin, we insure that collections A and B are cleared from any previous activity that may have involved them.

                clear a                                    Any previous subset isolations are
                clear b                                    now cleared from collections A and B

Now we want to carefully select two sets of records from the CUSTOMERS table so that there is a known overlap between them. We will put one subset in collection A and the other in collection B. The PICK command selects records by record number so we will use the PICK, and also show the command response that you see if you were doing this by hand.

                pick a customers 1-7BA
                7 records selected
                pick b customers 4-10
                7 records selected

Here are two subset selections, one in collection A and the other in collection B. They are picked by record number so there is a known overlap between them. We will study the action of queries below by examining the record numbers in the records that result.

Even though these selections were obtained by isolating records 1 thru 7 in A and records 4 thru 10 in B, they could have been anything. Say A was obtained by HAIR_COLOR=RED and B was HANDED=LEFT. Then, in the above illustration box A represents all redheads in the database and box B represents all the left-handed in the database. Where they overlap represents all the records for which both conditions are true, all left-handed redheads.

Lets look at these two subsets:

rpr/r a name

1 CUSTOMERS SMITH, BOB
2 CUSTOMERS WOMAN, WONDER
3 CUSTOMERS JOHNSON, WILLIAM Q.
4 CUSTOMERS STRONG, BARBARA
5 CUSTOMERS LANE, LOIS
6 CUSTOMERS WHITE, PERRY
7 CUSTOMERS DANDY, JIM

The RAWPRINT (rpr) command
                shows us what is in A and what
                is in B. The r option on the
                command causes each record's
                number and the table name
                from whence it comes to be
                displayed. And the command
                itself specifies the collection

rpr/r b name

4 CUSTOMERS STRONG, BARBARA
5 CUSTOMERS LANE, LOIS
6 CUSTOMERS WHITE, PERRY
7 CUSTOMERS DANDY, JIM
8 CUSTOMERS DOE, JOHN
9 CUSTOMERS MARVEL, CAPTAIN
10 CUSTOMERS KENT, CLARK

and what field(s) to print. In
this case, we asked for just
one field, NAME.

Note that records 4, 5, 6, and
                7 are the overlap between
                these two subsets.

The AND operation:
                c = a and bBA
                4 records selected
                rpr/r c name

4 CUSTOMERS STRONG, BARBARA
5 CUSTOMERS LANE, LOIS
6 CUSTOMERS WHITE, PERRY
7 CUSTOMERS DANDY, JIM

Collection C now contains records which were common to A and B, records 4 thru 7. In our boxes, the shaded area represents what was selected, all left-handed red heads.

So AND specifies the intersection of two subsets: records that are true for both.

The OR operation:
                c = a or bBA
                10 records selected
                rpr/r c name

1 CUSTOMERS SMITH, BOB
2 CUSTOMERS WOMAN, WONDER
3 CUSTOMERS JOHNSON, WILLIAM Q.
4 CUSTOMERS STRONG, BARBARA
5 CUSTOMERS LANE, LOIS
6 CUSTOMERS WHITE, PERRY
7 CUSTOMERS DANDY, JIM
8 CUSTOMERS DOE, JOHN
9 CUSTOMERS MARVEL, CAPTAIN
10 CUSTOMERS KENT, CLARK

Collection C now has records that were either in A or B. We have lumped them together in one place. Along with the red headed lefties, right-handed redheads are selected as well as blondes and brunettes as long as they are left-handed.

So the OR specifies the union of two subsets: records that are true for either or both.

The XOR operation:
                c = a xor bBA
                6 records selected
                rpr/r c name

1 CUSTOMERS SMITH, BOB
2 CUSTOMERS WOMAN, WONDER
3 CUSTOMERS JOHNSON, WILLIAM Q.
8 CUSTOMERS DOE, JOHN
9 CUSTOMERS MARVEL, CAPTAIN
10 CUSTOMERS KENT, CLARK

Collection C now contains the "exclusive or." We have got all the right-handed read heads or the left-handed anything else, but no red headed lefties.

The XOR isolates the exclusive union of two subsets, isolating records that are true in either, but not in both.

The EXCEPT operation:
                c = a except bBA
                3 records selected
                rpr/r c name

1 CUSTOMERS SMITH, BOB
2 CUSTOMERS WOMAN, WONDER
3 CUSTOMERS JOHNSON, WILLIAM Q.

This is the "but not." Collection C now contains only right-handed red heads.
EXCEPT specifies records that are true for one but not for the other.

                c = b except aBA
                3 records selected
                rpr/r c name

8 CUSTOMERS DOE, JOHN
9 CUSTOMERS MARVEL, CAPTAIN
10 CUSTOMERS KENT, CLARK

Another "but not." C now has only left-handed people but no red heads.
EXCEPT specifies records that are true for one but not for the other.

Mixed operators:
                c = a xor b and aBA
                3 records selected
                rpr/r c name

1 CUSTOMERS SMITH, BOB
2 CUSTOMERS WOMAN, WONDER
3 CUSTOMERS JOHNSON, WILLIAM Q.

This was a silly thing to do as it accomplished exactly the same thing as "a except b" and is much more eloquently stated that way rather than as shown above.

But we did want to show you that you can string out ORs, ANDs, and XORs in a single query expression. You will have use for that. Also note that collection A was used twice in the query expression. You will use that, too. As a matter of fact, we could even have said A = A XOR B AND A (instead of C=). In a case like that A would not only be part of the expression but also its result. We will make use of that further below, we haven't so far because we wanted to leave the contents of A unaltered so we could keep reusing it.