esProc has some special ways to produce more orderly and more readable code, among which long statements and computational sub-statements will be illustrated in this article.
1. Long statements
If a long expression cannot be fully displayed when written in a single cell, one can write it in multiple cells according to sequential cells rule, which, in a computational cell or an execution cell, allows the code to automatically move on to the next cell if it ends with characters including “,”, “;” and “(” until it doesn’t ends with these characters or reaches the end of the row (or the code block). For example:
| A | B | C | |
| 1 | ==[“one”,”two”, | “three”,”four”, | “five”] |
| 2 | ==create(Field1, | Field2, | Field3) |
| 3 | ==demo.query( | “select NAME, | STATEID from CITIES”) |
| 4 | ==A3.groups( | STATEID; | count(~):Count) |
| 5 | >>C5=A4.( | Count) |
In the example, each row is a long statement. A long statement begins with double equals sign “==” or double greater-than symbol “>>” and tells esProc to write the expression according to sequential cells rule. The computed result of an expression beginning with double equals sign will be stored in the first cell. Results of A1, A2, A3 and A4 are as follows:
The long statement in A5 is used to assign value to C5, which is shown as follows:
As can be seen from the example, the long statements can use various functions. The only thing one should note is that, except the last one, all cells should end with “,”, “;” and “(“.
A long statement can show the structure of an expression clearer when one uses functions, particularly if and case, to write code. For example:
| A | B | C | D | |
| 1 | Position | F | ||
| 2 | ==case(B1, | “C”:”Center”, | “F”:”Forward”, | “G”:”Guard”) |
| 3 | Month | 7 | ||
| 4 | ==if(B3<=3:”Q1″, | B3<=6:”Q2″, | B3<=9:”Q3″, | “Q4”) |
A2 changes the abbreviations for different positions of the players in a competition to the corresponding words. A4 computes which quarter the month in B3 belongs to. Results of A2 and A4 are respectively as follows:
Besides being in a single row, a long statement can be also used in a code block. In this case, the first cell of the long statement is the master cell, and all the cells in the code block are extended cells. For example:
| A | B | C | |
| 1 | Air Quality Index | [59,164,50,104,93] | |
| 2 | =B1.(func(A3,~)) | ||
| 3 | func | ==if( | A3>300:”Hazardous”, |
| 4 | A3>200:”Very Unhealthy”, | ||
| 5 | A3>150:”Unhealthy”, | ||
| 6 | A3>100:”Unhealthy for Sensitive Groups”, | ||
| 7 | A3>50:”Moderate”, | ||
| 8 | “Good”) | ||
| 9 | return B3 |
In this example, the subroutine in A3 uses a long statement to return the corresponding levels according to the air quality indexes (AQI). B3 is the master cell of the cell block in which the long statement resides and C3~C8 includes all the extended cells. A2 calls the subroutine to compute the quality grades corresponding to the AQI data. The result of A2 is as follows:
esProc will handle the code block that takes the first cell of the long statement as its master cell as a single statement; otherwise the program will execute each cell in the code block and may produces an error.
2. Computational sub-statements
A subroutine was used in the above to compute the grades for air quality corresponding to the AQI data. Its function is to compute the grade for air quality corresponding to each piece of AQI data. One can also write the expression in A2 directly as =B1.(if(~>300:”Hazardous”,~>200:”Very Unhealthy”,~>150:”Unhealthy”,~>100:”Unhealthy for Sensitive Groups”,~>50:”Moderate”,”Good”)) rather than use the subroutine, but the code seems complicated and overloaded and not easy to understand. Besides the subroutine, a computational sub-statement can be used in esProc to handle the computation:
| A | B | C | |
| 1 | Air Quality Index | [59,164,50,104,93] | |
| 2 | ==B1.(??) | =if( | A3>300:”Hazardous”, |
| 3 | A3>200:”Very Unhealthy”, | ||
| 4 | A3>150:”Unhealthy”, | ||
| 5 | A3>100:”Unhealthy for Sensitive Groups”, | ||
| 6 | A3>50:”Moderate”, | ||
| 7 | “Good”) |
A2 gets the same result as the code in the above does. One can notice that the coding form of the computational sub-statement is similar to that of a subroutine. The former’s master cell begins with double equals sign “==”, which means it is not necessary to add the double equal sign or double greater-than symbol before a long statement used in the code block that uses the same master cell. The double question marks “??” is used in the master cell of the computational sub-statement, and the value of the computational cell finally executed in the code block will be returned automatically and no return statement is needed. In addition, a subroutine can be called from anywhere in a cellset, but a computational sub-statement can be called only in the master cell.
The computational sub-statement in the above only consists of a single long statement which uses the if() function. But the code block of a computational sub-statement can also hold multiple statements. For example:
| A | B | C | |
| 1 | Air Quality Index | [59,164,50,104,93] | |
| 2 | ==B1.(??) | if ~>300 | =”Hazardous” |
| 3 | else if ~>200 | =”Very Unhealthy” | |
| 4 | else if ~>150 | =”Unhealthy” | |
| 5 | else if ~>100 | =”Unhealthy for Sensitive Groups” | |
| 6 | else if ~>50 | =”Moderate” | |
| 7 | else | =”Good” |
In this case, the code block of a computational sub-statement includes more than one computational cell. Each computation will return the value of the computational cell finally executed. Take 93 as an example, =”Moderate” in the computational cell C5 is executed finally in the code block, so the returned result is Moderate. Note that expressions, instead of constants, should be entered into the cells in Column C. A2 gets the same result as the A2 in the previous cellset:
Functioning as a special long statement, a computational sub-statement is to compute the expression in the master cell.
One can handle some complicated computations using the computational sub-statements. For example, based on the state data table – STATES – and big cities data table – CITIES, find the states where the total population of both the two most populated cities is greater than 2,000,000. The solution is as follows:
| A | B | C | |
| 1 | =demo.query(“select STATEID,ABBR, NAME from STATES order by STATEID”) | ||
| 2 | ==A1.select(??) | =demo.query(“select * from CITIES where STATEID=?”,STATEID) | |
| 3 | if B2.len()<1 | =false | |
| 4 | else if B2.len()==1 | =B2(1).POPULATION>2000000 | |
| 5 | else | =B2.sort(POPULATION:-1) | |
| 6 | =C5(1).POPULATION+C5(2).POPULATION>2000000 |
First A1 selects all the data from STATES. A2 uses the computational sub-statements to judge if each state meets the condition according to the results in the code block. In the computing process, B2 selects the big cities in a state and makes further judgment according to the result. If no big cities are found, use =false in C3 to return the result, meaning that no data meet the condition; if only one city is found, judge in C4 if this city’s population is greater than 2,000,000; if two or more cities are found, sort them in C5 by population in descending order and judge in C6 if they meet the condition. Therefore, by using the computational sub-statements, the structure of the complicated query becomes clearer. Result of A2 is as follows:
