The esProc table sequence is a structured two-dimensional table, having concepts of field, record, primary key and reference. These concepts originate from the data table of relational database. A table sequence is also an explicit set of genericity and orderliness, which can perform structured data computing more flexibly. A record sequence is the reference of table sequences. They are closely related and their usages are basically the same. The article will explain their basic computations from aspects of access, loop function, aggregate function and sets operations.
In the cellset below, read two-dimensional structured data from a file, create the table sequence object and store it in cell A1. Create the record sequence object by referring to A1 and store it in B1:
The following is the table sequence in A1 after computing. Only part of the data is displayed in the window. You can drag the scrollbar on the right to check the complete records:
The record sequence in B1 is as follows:
Note: The table sequence object can be created based on a database or a file, or be created by inserting records into an empty object. A record sequence originates from a table sequence but it doesn't store physical records. It only stores the references of some records in the table sequence.
1.2 Access field valuesIn the cellset below, get the field PName of the twentieth recording table sequence object A1 and store it in cell A2, and get the field PName of the second record in record sequence object B1 and store it in cell B2:
The results in A2 and B2 are as follows:
It can be seen that, as the second record in B1 is identical to the twentieth record in A1, so A2 and B2 have the same computed results. As can also be seen from the expressions of A2 and B2, both table sequence and record sequence have completely the same syntax for accessing fields.
A field name can be replaced by the field's sequence number and the result won't change. For instance:=A1(20).#3. Because this kind of replacement is employed universally in esProc, we won't go into details about it.
1.3 Access column dataIn the cellset below, fetch column PName according to the column name from table sequence A1 and store it in A2, and again from table sequence A1, fetch column PName and column Amount according to the column names and store them in B2. The record sequence and table sequence have the same expression when accessing the column data, so only the latter is selected for our illustration:
The results in A3 and B3 are as follows:
Using the syntax of T.(x), you can only fetch one column of data, and the computed result is a sequence without structured column name. With T.new() function, however, you can fetch one or more columns of data and the computed result is a table sequence with structured column names.
Whether the computing object is a table sequence or a sequence, both T.new() function and A.new() function will create a new table sequence. This means the computed result of B1.new(PName, Amount)is also a table sequence.
1.4 Access row dataIn the following, fetch the first two records from table sequence A1 according to row number and store them in A2, and fetch the first two records from table sequence B1 according to row number and store them in B2. Both the record sequence and table sequence in this example have the same expression for accessing row data:
The result of A2 is as follows:
The result of B2 is as follows: