The strings in esProc sometimes include some special identifiers that may cause ambiguity about the expression. So does the names of fields in a table sequence sometimes. The following will teach you how to deal with them properly.
1. Special identifiers in strings
A string may include various signs, some of which could bring trouble to the use of the string. First let’s look at problems you may encounter when using constant strings. For example:
| A | B | C | |
| 1 | =,>,<,+,- | ===== dividing line ===== | >100 |
| 2 | / is division sign | 34.55 | end |
Strings in A1 and B1 begin with “=” and will be interpreted as wrong expressions instead of constants if they are entered directly in cells. This can cause errors in computation. The string in C1 begins with “>” and won’t be handled as what it is; instead the cell will be regarded as an executable cell. The string beginning with “/” in A2 will be handled as a comment statement when written directly in the cell. The constant in B2 will be parsed into an integer automatically instead of a string. Being a reserved word, the string in C2 also won’t be handled as a string constant. In these cells, only cell B2 has a value; but the value is not of string type.
The above problems that string constants cannot be identified correctly are caused by two factors. In most cases the string itself is ambiguous, in other cases the string’s first character is a special identifier, such as “=”, “>”, “/” and etc. In esProc, you just need to add a single quotation mark (’) before the string constant to make it recognized normally.
| A | B | C | |
| 1 | ‘=,>,<,+,- | ‘===== dividing line ===== | ‘>100 |
| 2 | ‘/ is division sign | ‘34.55 | ‘end |
Values of A1, B1 and C1 are as follows. The single quotation mark at the front serves only as the mark of string constant cell, it won’t appear in the value:
Values of A2, B2 and C2 are as follows. Notice that the data in B2 are left-aligned and of string type:
A string used in an expression needs to be surrounded by double quotation marks (like “…”), thus the above problematic strings can be identified correctly when used in expressions. Besides, for a string including ” in it, put an escape character (\) before the double quotation marks of the string; the escape character is also needed before the back slash (\) of the string. For example:
| A | B | C | |
| 1 | =”===== dividing line =====” | =”/ is division sign” | =”34.55″ |
| 2 | =”\”How is Frank?\” he asked.” | =$[$\{B1}”C:\\test”] | =$[${B1}”C:\\test”] |
In the cellset, A1, B1 and C1 show the proper method of handling the special strings mentioned in the above. The use of double quotation marks in an expression is to indicate the type of the string and thus to remove the ambiguity. Values of cell A1, B1 and C1 are as follows:
The string in A2 includes double quotation marks, so put an escape character before each double quotation mark. Do the same thing before \ if a string has one. Value of cell A2 is as follows:
B2 uses the string constant $[…], instead of the double quotation marks, to represent a string. In this case, the double quotation marks won’t cause ambiguity and escape characters are not necessary; but an escape character is still needed before \. Note that there is another problem about the string constants: As a macro in the format of ${…} can be used in a string constant, an escape character should be added to distinguish the string from a macro when the latter is not wanted. You can see this more clearly by comparing values of cell B2 and cell C2:
It can be seen that when the expression in C3 is computed, ${B1} is used as a macro and B1’s value is entered in the string.
2. Special identifiers in field names
In a table sequence, a field name including non-letter and non-number special identifiers, like %, (,), ” or a blank space, could cause ambiguity when the expression is parsed. For example:
| A | |
| 1 | $(demo) select * from STATES order by STATEID |
| 2 | =A1.new(NAME:STATE,POPULATION,AREA:AREA(sq. mi.)) |
| 3 | =A2.(round(POPULATION/AREA(sq. mi.),3)) |
The following is the table sequence generated in A2, in which the AREA field is further explained by adding units to it:
When A3 computes the population density using the data in A2, something goes wrong when parsing the field name in the expression. To prevent this from happening, single quotation marks can be used to surround the name of the field in the table sequence (like ‘…’) so as to distinguish the identifier from the specific area of signs. For example:
| A | |
| 1 | $(demo) select * from STATES order by STATEID |
| 2 | =A1.new(NAME:STATE,POPULATION,AREA:AREA(sq. mi.)) |
| 3 | =A2.(round(POPULATION/’AREA(sq. mi.)’,3)) |
By doing so, esProc will handle the characters surrounded by the single quotation marks (as with ‘AREA(sq. mi.)‘) as a whole and A3 can get the desired result:
Besides the names of fields in a table sequence, the special identifiers represented in the form of ‘…’ can also be used when parameters with special names are called.
Of course the fields in a table sequence can be referenced by their positions to avoid the trouble brought by directly using the field names. For example:
| A | |
| 1 | $(demo) select * from STATES order by STATEID |
| 2 | =A1.new(NAME:STATE,POPULATION,AREA:AREA(sq. mi.)) |
| 3 | =A2.derive(round(POPULATION/#3,3):Density) |
A3 adds the column of Density to the table sequence in A2. The corresponding code uses #3 to reference A2’s second field so that the problem caused by special field name is solved. Result of A3 is as follows:
When a field in a table sequence is called with special identifiers represented by ‘…’, the field name is not allowed to contain single quotation marks; otherwise it can only be called according to the position as with the example above.