1. esProc’s function options
Many functions in esProc can use function options with which the same function can have different work patterns. The basic format of function options is f@o(…) in which o is f function’s option. For example:
| A | B | |
| 1 | 2004-5-5 | 2014-7-7 |
| 2 | =interval(A1,B1) | |
| 3 | =interval@y(A1,B1) | |
| 4 | =interval@m(A1,B1) |
We can use interval function to compute the number of days between two dates. In expressions in A3 and A4, @y and @m are interval function’s options. With these options, the function will use the year and the month as computational units in computing time interval. With function options, a function can meet multiple needs, extend its role and avoid too many function names or function parameters. The number of days, years and months between the two dates are computed respectively in A2, A3 and A4, as shown below:
2. Common function options
Some function options are common in esProc. They can be used by many functions.
@1 and @a
@1 option and @a option are frequently used by position, select and join functions , such as A.pos(), A.select(), A.pmax(), A.pselect(), A.minp(), P.align(), etc.
Use of @a option can make functions that return the first query result by default return all eligible results. Contrary to @a, functions that return multiple members by default will return only the first query result by using @1 option.
We’ll look at the use of these two options through some examples:
| A | |
| 1 | [1,2,3,4,3,2,1,2,3,2,1] |
| 2 | =A1.pos(2) |
| 3 | =A1.pos@a(2) |
| 4 | =A1.pselect(~==1) |
| 5 | =A1.pselect@a(~==1) |
The results in A2, A3, A4 and A5 are as follows:
| A | |
| 1 | =demo.query(“select * from CITIES”) |
| 2 | =A1.select(left(NAME,1)==”C”) |
| 3 | =A1.select@1(left(NAME,1)==”C”) |
| 4 | =A2.align([6,35,40],STATEID) |
| 5 | =A2.align@a([6,35,40],STATEID) |
The cities whose names start with a C are selected in A2:
The first city whose name starts with a C is selected in A3:
The first cities whose STATEIDs are 6, 35 and 40 respectively are selected in A4:
All cities whose STATEIDs are 6, 35 and 40 are selected and grouped by states in A5:
By the way, since digit 1 is difficult to distinguish from the lowercase letter l, in most cases, the former is used in esProc’s options.
One other thing need to be made known is that the same option in different functions can have different meanings. For example, @a used in position functions means returning all results, while in functions for file writing, like f.write() and f.export(), it means appending.
@z
@z option is often used in some functions related to order, like sort, position, select, etc. Such as A.rank(), A.sort(), A.pos(), A.pselect(), A.select() and so on.
| A | |
| 1 | [1,2,3,4,3,2,1,2,3,2,1] |
| 2 | =A1.pos@z(2) |
| 3 | =A1.sort@z() |
| 4 | =demo.query(“select * from CITIES”) |
| 5 | =A4.select@z(STATEID:5) |
With @z option, positioning or selecting data in a sequence or a table sequence will be executed from back to front. Thus in this example, A2 returns the position of the last 2:
A3 returns results of sorting in descending order:
Records obtained in A5 are also sorted in descending order:
@b
@b option is often used in functions to position, select, etc., like A.pos(), A.pselect(), A.select(), and so on. The use of @b option is usually accompanied by binary search algorithm, which is more efficient in query, with the prerequisite that A is ordered; otherwise results may be wrong.
| A | |
| 1 | =demo.query(“select * from CITIES”) |
| 2 | =A1.select@b(STATEID:5) |
| 3 | =A1.sort(STATEID) |
| 4 | =A3.select@b(STATEID:5) |
Since data in A1 is not sorted according to state ID, only one result is obtained in A2 with the binary search when @b option is used:
while A4 obtains the correct results because data is sorted in A3:
But it is another thing when @b is used in functions to read and write, such as f.import(), f.export(), f.cursor(). In this case, it is the binary files that being read in or written out. In esProc, binary files use less memory space and have faster access speed, therefore the use of binary files will bring higher efficiency.
3. Use multiple options simultaneously
Multiple options can be used simultaneously in esProc when needed. There is no specific order among these options. For example:
| A | |
| 1 | [1,2,3,4,3,2,1,2,3,2,1] |
| 2 | =A1.pos@az(2) |
| 3 | =A1.pos@za(2) |
| 4 | =demo.query(“select * from CITIES”) |
| 5 | =A4.select@1z(STATEID:5) |
Because both A 2 and A3 get positions of all the 2 in A1 from back to front, their results are identical:
A5 gets the first city whose STATEID is 5 from back to front:
Note that some function options are mutually exclusive , thus cannot be used simultaneously. Such as @a and @1, or @t and @b, options of f.import().