Creating Query Expressions - MapInfo_Pro - 2023

MapInfo Pro Help

Product type
Software
Portfolio
Locate
Product family
MapInfo
Product
MapInfo > MapInfo Pro
Version
2023
Language
English
Product name
MapInfo Pro
Title
MapInfo Pro Help
First publish date
1985
Last updated
2023-09-12
Published on
2023-09-12T16:39:16.995549

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:

  1. On the MAP tab, click on the Launcher button in the lower right corner of the Selection group to open the Select dialog box.
  2. Click the Assist button.
  3. In the Expression dialog box, type your expression directly into this box, or choose from the drop-down list selections to create an expression.
  4. Click Verify to verify that your expression is valid.
  5. Click OK to return to the Select dialog box.
  6. 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.