When using branch statements, we need to check one or multiple conditions, execute different code according to the results or return different results. In esProc, the if/else statement is used to realize branching. It has the following forms:
1. if x … else … in the same line
When condition x after if is true, the statement after it will be executed; otherwise the statement after else will be executed. The else part can be omitted. else and if must be in the same row. After execution, the value of the cell in which if is located is the computed result of x.
| A | B | C | D | |
| 1 | -14 | |||
| 2 | if A1>=0 | >A3=A1 | else | >A3=-A1 |
| 3 |
The result of A2 is as follows:
Since the result of A2 is false, the else part, that is, the code in D2, will be executed. The result of A3 is as follows after execution:
Actually, the result of A3 is the absolute value of the number in A1.
Sometimes, there is no else part. We only need to decide whether the code after if statement should be executed:
| A | B | C | |
| 1 | =demo.query(“select * from EMPLOYEE”) | ||
| 2 | for A1 | ||
| 3 | if age(A2.BIRTHDAY)>=40 | >A4=A4+1 | |
| 4 |
The code in A2 checks the information of every employee by loop and the code in B3 judges it. If the age of an employee is not less than 40, he/she will be counted in A4. The computation continues thus until the number of all employees whose ages are not less than 40 are worked out.
The following logical connectors can be used in the judgment statements:
a&&b
For the “a and b”, the result is true only on condition that both a and b are true.
a||b
For the “a or b”, the result is true only on condition that either a or b is true.
!a
For the “not a“, the result is true only on condition that a is false.
| A | B | C | |
| 1 | =demo.query(“select * from EMPLOYEE”) | ||
| 2 | for A1 | ||
| 3 | if age(A2.BIRTHDAY)>=40 && A2.GENDER==”M” | >A4=A4+1 | |
| 4 |
In A4, the number of male employees whose age is not less than 40 is computed:
2. Function if(x,a,b)
If x is true, expression a will be computed and the result be returned; otherwise, expression b will be computed and the result be returned.
Sometimes, the previously mentioned if…else… code can be replaced by if function in order to make the statement more concise.
| A | |
| 1 | -14 |
| 2 | =if(A1>=0,A1,-A1) |
3. if x … else … in the code block
If x is true, then the if block of code will be executed ; otherwise, the else block of code will be executed. You can certainly ignore the else part. else and if must be in the same column. Different from some other programming languages, esProc employs code block, instead of symbols such as {} or statements like end if, to determine the statement’s valid scope.
| A | B | C | |
| 1 | =demo.query(“select * from EMPLOYEE”) | ||
| 2 | Male | [] | 0 |
| 3 | Female | [] | 0 |
| 4 | for A1 | if A4.GENDER==”M” | >B2=B2|A4.(NAME+” ” +SURNAME) |
| 5 | >C2+=1 | ||
| 6 | else | >B3=B3|A4.(NAME+” ” +SURNAME) | |
| 7 | >C3+=1 |
As shown in the example, based on the judging results of B4, the if block of code composed of C4 and C5 and the else block of code composed of C6 and C7 are executed alternatively. They will compute respectively the name lists of male and female employees, as well as the numbers of male and female employees. Results can be seen in B2, B3, C2 and C3 after the computations are over:
4. Multiple blocks of if x … else if y …
This is code for multi-branch statements in esProc, which can be written repeatedly forever. The if/else must be in the same cell. Again the point is emphasized that there is no such corresponding statement of end if, and esProc employs the scope of code block to determine when if statement will be over.
| A | B | C | |
| 1 | 4 | * | 7 |
| 2 | if B1==”+” | ||
| 3 | >B12=A1+C1 | ||
| 4 | else if B1==”-“ | ||
| 5 | >B12=A1-C1 | ||
| 6 | else if B1==”*” | ||
| 7 | >B12=A1*C1 | ||
| 8 | else if B1==”/” | ||
| 9 | >B12=A1/C1 | ||
| 10 | else | ||
| 11 | >B12=”Error” | ||
| 12 | Result: |
A1, B1 and C1 combine to form an expression, which is computed in B12. The result is as follows:
5 . Nested Branch Statements
Branch statements can be used to make further judgment in the if or else block of code.
| A | B | C | |
| 1 | 2016-2 | ||
| 2 | =A1.array(“-“) | >year=A2(1) | >month=A2(2) |
| 3 | if [1,3,5,7,8,10, 12].pos(month)>0 | >A8=31 | |
| 4 | else if [4,6,9,11].pos(month )>0 | >A8=30 | |
| 5 | else if month==2 | if year%400==0 | >A8=29 |
| 6 | else if year%100!=0 && year%4==0 | >A8=29 | |
| 7 | else | >A8=28 | |
| 8 |
In the above example, a character string of year and month, with a separator “-”, is entered in A1, and the code after it computes the total days of the month. The computation should first judge which month it is. For February, further judgment is needed to determine whether it is a leap year. The computed result in A8 is as follows:
