Happy #MapInfoMonday,
Today I'll dive a bit into some of the features in the
SQL Window that we added with MapInfo Pro v2019. I'll look at the
Value dropdown that appears at the top of the window, just above the
Scripts Editor field.
The Values dropdown has been added based on several user requests from the
Precisely Ideas Portal. The idea is to help users specify the value they are searching for by creating a list of unique values in a column.
Now, the
Value dropdown doesn't give much meaning until you have specified a condition you want it to list the values for.
In my example, I'm looking for addresses on a certain road. You can see that I have specified the table to select from and started entering the condition in the image below. If I after entering "=" activate Intellisense by using the Ctrl + Space keyboard shortcut, MapInfo Pro will give me a very short list of possible values, typically less than 5.
If the value I am looking for isn't in that list, I can use the
Value dropdown to get a longer list.
As a table can contain thousands of unique values, you can filter the list by entering a search value in the Search field. In the example below, I have filtered the list using the word "Hul".
Because this feature shouldn't take too long to load the values, we have set some conditions on the load. It will start looking through the rows for 2 seconds or until it has found 1000 unique values. Whatever might happen first. This also means that for very large datasets, you will not see all the unique values initially. To load more values, click on the
Fetch more values button to the left of the condition.
The list will get updated with new unique values if any were found. Also, note the small popup that tells you the result of the search. In the case above no unique values were added. The text also tells you that MapInfo Pro hasn't searched the entire dataset again which prompts me to hit the button again.
Now after having searched through the entire dataset, another unique value was added to the list. I can now double-click on the value I want to use and it will get inserted into my query and the list closes. If I single-click the value, the value is inserted but the list stays open.
Notice that you don't have to worry about quotes. MapInfo Pro automatically adds quotes around the values if it's a string.
A final note: The
Value list doesn't work for column values only, you can also use it for expressions. The example shows how the
Value list now holds unique values for an expression that extracts the first three letters from the street name.
I hope you found this useful. If you want to learn more about the new SQL Window, read this post,
The New SQL Window, which gives you an overview of the SQL Window.
PS: For the upcoming MapInfo Pro v2021, we are working on a tool that will help you query your table based on attribute information. This tool will also include functionality similar to the Value list described in this article.
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------