Opening Database Tables Using Column and Row Filters - MapInfo_Pro - 2023

You can apply column and row filters on a database table before opening it in MapInfo Pro. Filters let you choose which columns and rows to fetch from the data source. You would do this to reduce the amount of information that you will work with and reduce the time it takes to fetch and save the data.

To apply column and row filters on a database table when opening it:

1. On the HOME tab, in the File group, on the Open list, click Table.
2. From the Files of type list, select the database to work with. The Select One or More Tables to Open dialog box opens.
3. In the Database Tables list, select a table to open.
4. Click Options.
5. In the Open DBMS Table Options dialog box, select Standard Mode to open particular rows or columns of the selected table and then click Column Filters.

   The Column Picker dialog box opens.

6. Choose which columns to fetch from the table and then click OK.

   For information about setting column filters, see Setting a Column Filter.

7. Select Row Filter.
8. In the Row Picker dialog box, choose which rows to fetch from the table and then click OK.

   For information about setting row filters, see Setting a Row Filter.

9. If working with large data files, larger than 2GB, then under MapInfo TAB File select MapInfo Extended from the Format list. You can also select the character set of the data file from the Charset list.
   Note: The default Format and Charset settings are software preferences, see Setting Your Language Preferences.
10. Click OK to close the Open DBMS Table Options dialog box.
11. From the Preferred View list, select how you want to open the tables.

    Select New Mapper or Current Mapper to open them in a Map window, or Browser to open them in a Browser window to see the table contents.

    For more details, see this step in the instructions under Opening a DBMS Table.

12. Click OK.

    If a MapInfo message dialog displays asking if you want to overwrite the existing file, click OK. You have previously opened this file and there is a temporary copy of the file on your machine. MapInfo Pro will overwrite this temporary copy.

The table opens in MapInfo Pro with only the columns and rows that you selected to fetch.

To create your own table filter in standard SQL syntax, see Creating Your Own Table Filter.

Setting a Column Filter

To specify the columns to download in the Select One or More Tables to Open dialog box, click Options and then click Column Filter. This opens the Column Picker dialog box.

When the Column Picker dialog box first opens, an asterisk (*) displays in the Selected list. If you leave the asterisk, MapInfo Pro retrieves all the columns in the table from the remote database.

To select which columns to retrieve:

1. Move the columns you want to display from the table from the Available group to the Selected group using the arrow buttons. The single arrow keys move one selected column at a time. The double arrow buttons move all the columns at once from one list to the other. You can also double click the column to move it from one list to another.
2. Change the order of the selected columns in the Selected list by highlighting the column you want to move and clicking Up and Down until the column is positioned correctly.
3. Click OK to complete your selections.

If the table is mappable, the Available list displays an additional column called OBJECT, which refers to the spatial column:

• DBMS users can select it to download point objects from the table. If the table is spatial, MapInfo Pro downloads lines, points, and polygons depending on what the table contains.
• SQLite users can select it to download spatial objects from the SQLite database table.

Setting a Row Filter

In the Select One or More Tables to Open dialog box, click Row Filter to select the row data that you want MapInfo Pro to download. This opens the Row Picker dialog box.

To retrieve all of the rows pertaining to the columns you selected, leave this dialog box as is.

To select the column, operator, and value entries that match the data you want to retrieve from the selected table:

1. In the Column list, choose the column(s) from which you want to filter rows. (This is the same as specifying the WHERE clause in a SQL query.) If you are filtering rows for one column, select a column, operator, and value. If filtering rows on more than one column, select a column from the next drop-down list. This also activates the next row of fields for data entry.
2. Use the Operator and Value boxes to choose how you would like to filter the rows.

   For more information about how to complete these entries, see Notes for Completing the Row Picker Query.

3. Click OK when you have finished filtering the rows.

Notes for Completing the Row Picker Query

The Operator list boxes contain all supported operator symbols for their corresponding selected column. The list of operators will vary depending on the type of the selected column. For example, the operators <, >, and = (among others) will be available for numeric columns, but object columns will have only the operator WITHIN.

The type of the Value control also varies depending on the column type. For object columns, this control will be a list box containing the values CURRENT_MAPPER and SELECTION. For all other column types, this control will be an edit box, allowing the user to enter the proper type of data.

If you select an object column using the `within' operator, MapInfo Pro returns only the objects from the server that are within the selection. Multiple objects are automatically combined into a single object before performing the spatial query, which returns the correct results. The object is stored in the .TAB file so that it is re-used the next time you open the .TAB file to the remote database.

There are two wildcard characters that can be used with the LIKE operator: `%' and `_'. The `%' wildcard character matches zero or more characters. The `_' wildcard character matches only one character.

When rows are being filtered from more than one column, Conjunction becomes available. Choose one of these Boolean operators: AND, OR, AND NOT, OR NOT.

Creating Your Own Table Filter

To create your own filter when opening database tables,

1. Open a database on the HOME tab, in the Open list, click Table, and then selecting a database to open from the Files of Type list.
2. Click Open to open the Select One or More Tables to Open dialog box.
3. Select the table that you want to open from the list of database tables.
4. Click Options to open the Open DBMS Table Options dialog box.
5. Select Expert Mode.
6. Click SQL View to open the Enter SQL Query dialog box.
7. In the SQL Query field, enter your query in standard SQL syntax, or click Load and open a SQL Query (*.sql) file.

   To type in multi-line formatted SQL do the following:

   • To get a new line press Enter.
   • To tab, press Ctrl+Tab.
   • To cut and paste from the clipboard, press Ctrl+X to cut, Ctrl+C to copy, and Ctrl+V to paste.
8. Click OK to apply your query and close the dialog boxes.