To perform a Select query, you must create an expression. An expression is a logical statement that is used to ask your question. For example, you have a table of apartments for rent. If you want to create a temporary table of all apartments that cost less than $800 a month, you could use the expression:
- RENT < 800
where RENT is the name of a numeric column that contains the per month rent rate.
MapInfo Pro searches the apartment table for all records that satisfy this condition and puts those records in a temporary table that you can map, browse, or carry out additional queries.
You can also perform mathematical operations on your data. For example, you want to create a temporary table of all apartments that have a total cost of less than $800. Total cost is equal to the sum of the rent and the monthly utilities.
You could use the expression:
- RENT + UTILITIES < 800
You are telling MapInfo Pro to add the number in the RENT column and the number in the UTILITIES column and then compare that number to 800.
For more information, see Creating Expressions for details in creating a variety of expressions.
Specifying an Expression
There are two ways of creating an expression. The first method is to type in the expression directly. When you are creating very simple expressions, this method is usually faster. The second method is to click the Assist button in the Select dialog box and build your expression using the pop ups in the Expression dialog box. This method is particularly useful when you are just learning how to build expressions or when you are building very complex expressions.
To specify an expression:
- On the MAP tab, click on the Launcher button in the lower right corner of the Selection group to open the Select dialog box.
- Click the Assist button.
- In the Expression dialog box, type your expression directly into this box, or choose from the drop-down list selections to create an expression.
- Click Verify to verify that your expression is valid.
- Click OK to return to the Select dialog box.
- Click OK to run the expression.
The Expression dialog box gives you three drop-down lists that you can use to build your expression: columns, operators, and functions.
Columns
This pop up lists every column in the table from which you are selecting. If the table contains derived columns from previous queries, those columns will also be listed.
Operators
This pop up contains mathematical and logical operation symbols. The mathematical operators in this pop up include addition, subtraction, multiplication, division, greater than, less than, and equal signs. You can use these symbols to create mathematical formulas. For example, from your table of sales representatives you want to select those sales representatives who, on the average, gross more than $2000 per month. Gross sales is computed by adding together sales and commission.
You have two columns in your table: TOTAL_SALES, which is total sales for the year for each representative and COMMISSION, which is total commission for the year for each representative. You could build the following expression:
- (TOTAL_SALES + COMMISSION) / 12 < 2000
This expression tells MapInfo Pro to add the number in the TOTAL_SALES column with the number in the COMMISSION column. However, this gives you gross sales for the year. We want average gross sales for the month. Therefore, we divide the sum by 12, which will give us a monthly average. We then compare that figure with 2000.
The Operators pop up also includes logical operators conjunctions AND, NOT, OR and LIKE. The LIKE operator can be used with two wildcard characters: `%' and `_'. The `%' character matches zero or more characters. The `_' character matches only one character.
Functions
This pop up contains mathematical functions that take one or more parameters and return a value. You use functions to perform basic mathematical functions on the data in that column. For example:
- abs(<number>)
takes the absolute value of the numbers in the specified column.
For example, a meteorologist wants to select all days where the temperature in her city was more than 10 degrees warmer or cooler than the national average. She has a column in her table, AVG_DIFF, that contains the difference between the national average and city average.
She could create the following expression:
- AVG_DIFF < -10 Or AVG_DIFF > 10
This expression tells MapInfo Pro to select all records that have an average difference less than -10 or greater than +10. However, she could also create the following expression:
- abs(AVG_DIFF) > 10
This expression tells MapInfo Pro to select all records where the absolute value of the average difference is greater than ten.
The Functions pop up contains many other functions, including area, perimeter, sin, cos, and date-related functions. For a complete list of functions, see Creating Expressions.
Verify
This button reviews the expression you have created and verifies that it is valid. This is particularly helpful if you are new to writing expressions.