SQL Window - 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
ft:lastEdition
2023-09-12
ft:lastPublication
2023-09-12T16:39:16.995549

The SQL Window can be used to create and run SQL Select, Insert, Update and Delete statements that can be saved for later re-use. You can also create MapBasic scripts that contain multiple SQL and other MapBasic statements. It can be divided into the following sections:

Scripts

The Scripts collapsible section consists of a combo-box containing a list of SQL scripts saved to disk along with scripts that are still being written and not saved. For details, see Working with SQL Scripts. The Open a saved script button adds a script to the list from a file (.QRY or .MBS) and makes it current. The Create new script split-button loads a new script template of a specific type (Select, Insert, Update, Delete, Script) in the script editor.

Assist

The Assist section keeps track of your recent activities and provides a one-click way to reuse and add items to your scripts. It keeps the last five recent and most used items of each intellisense type. When new tables are opened, they are also added. The default list is based on each script type with commonly used keyword and operators, etc. This section also contains the current distance and area unit. Clicking on an item in the list adds it at the current cursor position.

The section is collapsible but expands using a wrap panel, you can resize, and scroll-bars will appear. The color of the item text reflects syntax coloring. A context menu allows you to clear the list or reset it back to default. Individual Items can be removed by clicking the x when the mouse is over an item. You can also add custom items by selecting text in the SQL script editor and using the context menu item, Add to recent items.
Note: Use shortcut "Alt+A" to put focus on the Assist pane.

SQL window drop-downs

This section shows drop-downs similar to what we have in the SQL select and expression builder dialog-boxes. The following helper drop-down options are available:
  1. Table - Lists all the open tables except raster or grid, WMS, WMTS, and tile server. It stays open so that you can pick one or more tables using a single-click or Enter. Double-clicking or using ctrl+enter adds the selected table at the current cursor position in the script editor, closes the drop-down, and the focus goes to the script editor.
  2. Joins - Lists the suggested joins when more than one table is selected. The drop-down is single select only if two tables are selected, and multiple select if more than two tables are selected.
    1. If there is a selection, it replaces it.
    2. If no where clause, it appends "\r\nWhere".
    3. If empty where clause, it just appends join
    4. If non empty where clause, it appends "AND" if word following cursor is not in (and, or, noselect, into, hide, group, order)
    5. If non empty where clause, it prepends "AND" unless preceding word is (where, and, or)
    Spatial joins are shown first in the list if tables are mappable, followed by attribute joins. A spatial join is suggested between tables based on the predominant object type of each table.
    • The types of joins available are Contains, Contains Entire, Intersects, Within, Entirely Within, and Within a Distance.
    • Both directions are suggested, for example, if tables selected are world and world_caps, then "world contains world_caps" and "world_caps within world" are both suggested.
    • Exception is the Within a Distance join. This is only suggested in one direction; the same direction as the table projection list. A distance value is suggested and is based on the distance between the centroids of each table's bounds.
    • The spatial join is then ranked based on the number of rows each table has: if the first table has fewer rows than the second table, it is ranked higher.
    An attribute join is suggested between tables when:
    1. The column data types match.
    2. If the matching data type is char, and both columns have the same char width.
    3. The column names (headers) are exact matches or close matches (for example, column 1 name = "state_name" and column 2 name = "state" are a close match).
    4. Both directions are suggested, for example, if tables selected are world and world_caps, then "world.county= world_caps.county" and "world_caps.county= world.county" are both suggested.
    5. The object, float, and decimal column data types are ignored.
    The attribute join is ranked based on the following:
    1. An exact column name match is ranked higher than a close match, for example, if col1 name = "state_name" and col2 name = "state_name", the rank is increased by 1, whereas if col1 name = "state_name" and col2 name = "state", the rank is not increased.
    2. When both columns are indexed, the rank is increased by 2.
    3. When one column is indexed, the rank is increased by 1.
    4. If col1 comes from table with fewer rows than col2, the rank is increased by 1.
    Both sets of Joins are then ordered from highest to lowest rank, and clipped to at most 10 each.
  3. Column - Lists all the columns from the currently selected tables. This drop-down is disabled if no tables are selected in the Tables drop-down. Just like the Tables drop-down, this drop-down stays open so that you can pick one or more columns using a single-click or Enter. Double-clicking or using ctrl+enter adds the selected column at the current cursor position in the script pane, closes the drop-down list, and the focus goes to the script editor.
  4. Operator - Lists all the available SQL arithmetic, comparison, and logical operators. Click to add the selected operator at the current cursor position in the script editor and close the drop-down.
  5. Aggregate - Lists all the available SQL aggregate functions. Click to add the selected aggregate function at the current cursor position in the script editor and close the drop-down.
  6. Function - Lists all the available MapBasic functions. Click to add the selected MapBasic function at the current cursor position in the script editor and close the drop-down. Additionally, this drop-down contains a search box at the top that you can use to filter the list of MapBasic functions in the drop-down by typing into the search box.
  7. Value - The expression is set whenever a column or expression is picked using the Column drop-down or intellisense. Also, if some text is selected when the drop-down is opened, it is used as the expression. This drop-down stays open so that you can pick one or more values using a single-click or Enter. Double-clicking or using ctrl+enter adds the selected column at the current cursor position in the script pane, closes the drop-down list, and the focus goes to the script editor (multiple values should be comma-separated). The drop-down is populated with the first 1000 unique values or with values returned in first two seconds. Use the Refresh button to search for more records. If you enter some text in the search box, only records containing that value are returned.

The Drop-downs each have a keyboard shortcut (only when focus is in the text-box).

Drop-Down Shortcut
Table CTRL+Shift+T
Join CTRL+Shift+J
Column CTRL+Shift+C
Operator CTRL+Shift+O
Aggregate CTRL+Shift+A
Function CTRL+Shift+F
Value CTRL+Shift+V

Scripts Editor

The Scripts Editor is a RichTextBox where you write your SQL scripts. It is a feature-rich editor supporting syntax highlighting, syntax validation, different color configurations for scripting items like Tables, Columns, Functions Operators, etc. It also supports intellisense using "ctrl+space".

Note: Use shortcut "Alt+S" to put focus on the Scripts Editor.

Scripts Message Area

Displays errors or exceptions when a script is executed. Syntax validation results and elapsed time are also displayed here.

Output Options Area

At the bottom of the window, there are several buttons:
  • Browse
    • For Select scripts, creates a new browser window with the results.
    • For other script types, tries to find the table referenced in an existing browser window and brings that window to the front. If no browser window could be found, then a new browser for the table is created.
    • Button is selected by default.
  • Map
    • For Select scripts, adds the results as a new layer to the top-most map window and brings that map to the front, or creates a new map window if none exist..
    • For other script types, adds the table referenced by the script to the top-most map window if that map doesn't already contain a layer for it, then brings that map window to the front.
  • Color Override - When adding the results of a Select statement to map window, applies a color override with the selected color. The color override control is only visible for Select scripts.
    • Overrides are off by default: shows "None" and a Paint Can icon to denote that the control is for applying color.
    • Click on the split button to open the pop-up to select different modes.
      • First radio button turns off color override, icon changes to show Paint Can and "None".
      • Second radio button switches to "Automatic" mode where colors are initialized to a Color Brewer qualitative color group (8 colors), icon changes to show the next color with a gradient border.
        • This list of colors is shownin the color palette picker in the "Automatic Colors" section.
        • Each time a script is executed that includes the Map option, the currently selected color is applied to the override styles, and the color picker will select the next color from the "Automatic Colors" group.
        • Any of these colors can also be selected as the manual color.
    • Third radio button switches to "Manual" mode using the color currently shown by the palette picker, icon changes to the selected color with no border.
      • Or you can click directly on the color palette to switch to "Manual" and select the new color.
      • Color remains sticky.
  • Find - Finds results in the map and browser windows.
  • Run button - Executes the script. You can also use the following shortcuts to execute a script:
    • Ctrl+Enter executes the script and returns the focus back to the text-box.
    • Ctrl+Shift+Enter executes the script and the focus remains with the map or browser windows opened/added to.