5.2 Long Statements and Sub-statements

Read(532) Label: long statement,

esProc has some special ways to produce more orderly and more readable code. Here long statements and sub-statements are illustrated.

Long statements

If a long expression cannot be fully displayed when written in a single cell, it can be written in multiple cells according to sequential cells rule, which, in a calculation cell or an executable cell, allows the statement to automatically flow to the next cell each time when it is paused by the character "," or ";" until no such characters appear or the statement reaches the end. 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 this cellset, each row is a long statement. A long statement begins with a double equals sign "==" or a double greater-than symbol ">>", telling esProc to write the expression using sequential cells rule. The result of an expression preceded by the double equals sign will be stored in the first cell. Results of A1, A2, A3 and A4 are as follows:

 

 

A5’s long statement assigns value to C5, as shown below:

A long statement can use various functions. Except the last one, all cells should end with ",", ";" or "(".

A long statement makes the structure of an expression in if and case functions clearer:

 

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 full words. A4 computes which quarter the month in B3 belongs to.Here’re results of A2 and A4:

 

Besides being contained in a single row, a long statement can also be used in a multi-row code block. In this case, the first cell of the long statement is the master cell, and all the other non-blank 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 cellset, A3’s subroutine uses a long statement to return the corresponding air quality levels according to the air quality indexes (AQI). B3 is the master cell of the code block accommodating the long statement and C3~C8 covers the extended cells. A2 calls the subroutine to compute the quality levels of a batch of AQI data. Here’s A2’s result:

esProc handles the code block that takes the first cell of the long statement as its master cell as a single statement. An error could arise if the program executes the cells in a code block one by one.

Sub-statements

A subroutine was used in the previous example to compute the air quality levels of a batch of AQI data. It computes the air quality level for each AQI data item. Instead of using a subroutine, A2 may encompass all criteria into one expression as =B1.(if(~>300:"Hazardous",~>200:"Very Unhealthy",~>150:"Unhealthy",~>100:"Unhealthy for Sensitive Groups",~>50:"Moderate","Good")). The code looks complicated and overloaded, and difficult to understand. Besides the subroutine, a 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(

~>300:"Hazardous",

3

 

 

~>200:"Very Unhealthy",

4

 

 

~>150:"Unhealthy",

5

 

 

~>100:"Unhealthy for Sensitive Groups",

6

 

 

~>50:"Moderate",

7

 

 

"Good")

A2 gets the same result. You can notice that the sub-statement looks similar to a subroutine. The master cell of a sub-statement should begin with the double equals sign "==", and it defines a code block within which a long statement is accommodated without being preceded by a double equals sign "=="or a double greater-than symbol ">>". The master cell of the sub-statement uses the double question marks"??" and the last executed calcualtion cell in the code block will automatically return its value without the need of the return statement.In addition, a subroutine can be called across a cellset, but a sub-statement can be called only in the master cell.

The sub-statement in the above only consists of one long statement which uses the if() function. But a sub-statement code block 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 sub-statement code block contains more than one calculation cell. Each computation will return the value of the last executed calculation cell. Take 93 as an example, the calculation cell C6 containing ="Moderate" is the last executed cell 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 previous cellset:

Functioning as a special long statement, a sub-statement is to compute the expression in the master cell.

You can handle complicated computations using 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 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)

6

 

 

=C5(1).POPULATION+

C5(2).POPULATION>2000000

A1 retrieves all data from STATES. A2 uses a sub-statement to judge if a state meets the condition according to the result of the code block. B2 selects the big cities in a state to make further judgment. If no big cities are found in a state, use =false in C3 to return the result that this state doesn’t meet the condition; if only one big city is found, check if this city’s population is greater than 2,000,000 in C4; if two or more cities are found, sort them in C5 by population in descending order and then in C6 check if the condition can be met. By using the sub-statement, the structure of a complicated query becomes clearer. When the computation finishes, A2 gets the following result: