You formulate expressions using column (field) names and constants (for example, specific data values), on the one hand, and functions and operators, on the other. Think of the column names and constants as nouns, and functions and operators as verbs, prepositions and conjunctions. You always need at least one column name or a constant in an expression. How many operators and functions you need depends on what you want to do with your expression.
The simplest possible expression consists of a column name, for example:
- POP_1990
- STATE
You could use such an expression in Thematic Mapping to indicate what data is to be represented on the map. In Update Column such an expression would tell MapInfo Pro what data to use in updating a column in the target table.
Here are some slightly more complex expressions:
- POP_1990 > 17893
- POP_1990 <= POP_1980
- COUNTY <> "Orange"
- POP_1980 * 1.2
- POP_1990 / TOTAL_AREA
- round(POP_1990/TOTAL_AREA,.1)
The first three examples use comparison operators. The first tests to see whether the 1990 population is greater than (>) some constant (17893). The second tests to see whether the value of one column, POP_1990, is less than or equal to (<=) the value of another column, POP_1980. The third tests to see whether or not the county is Orange. When the county does not equal (<>) "Orange" the record is selected. You could use any of these expressions in Select or in the Where Condition clause of SQL Select. These commands allow you to select a subset of the records in a table. The expression defines the characteristics of the subset.
Examples 6 and 7 use arithmetic operators. Example 6 multiplies (*) the value of POP_1980 by a constant (1.2) while example 7 divides one column, POP_1990, by the value of another column, AREA.
Example 8 uses the round function to round the value of the expression "POP_1990/TOTAL_AREA" to the nearest tenth (.1).
Expressions 6, 7 and 8 do not have comparison operators and therefore they would not be suitable for use in Select or in the Where Condition clause of SQL Select. However, you could use them alone in Thematic Mapping, Update Column, or Select columns in SQL Select.
Setting Filter Criteria for Expressions
A filter criterion is a logical expression that usually compares a column value against some other value. For example, the following filter criterion uses the greater-than operator (>) to test whether the Order_Amount column has a value greater than one hundred:
Where Condition: Order_Amount > 100
If a query includes the preceding Where Condition clause, MapInfo Pro selects only the rows that have an Order_Amount value greater than one hundred.
The Where Condition field can contain two or more logical expressions if the expressions are separated by the word And or by the word Or. If the expressions are joined by the word And, MapInfo Pro only selects the rows that satisfy both criteria. If the expressions are joined by the word Or, MapInfo Pro selects any row that satisfies either criterion.
Filter criteria can use any column in your base table(s), regardless of whether you included the column in the Select Columns field.
Columns can be referred to by name or by number, where the number designates the order the column has in Select Columns. Thus, "col1" and "col6" refer to the first and sixth columns, respectively. The number must be preceded by the letters "col".