Pitney Bowes
MapInfo Pro™ Monthly Journal
"Be the Query Ninja" - An alternative to "OR"!

ninja The Query Ninja is back with a slick way to select records that meet multiple criteria. The idea here is to provide an alternative to using the "OR" operator. I'll describe this with an example but you should try and 'translate' this into a relevant use with your own data.

The following query examples will use cities and countries as seen in the screen shots below.

map
browser

The map and data shown here is the WorldInfo mapping product from Pitney Bowes Software. More info on our data products can be found at www.mapinfo.com/data.

Selecting all of the cities from a particular country might look like this:

sql

Using the Or() operator

Let's say you want to select all of the cities from both Guatemala and Honduras. You may be aware of the Boolean operators, AND, OR and NOT, that are available in MapInfo Professional. Below is how you can select all of the cities from both Guatemala and Honduras using the OR operator.

sql

If you then need to add a third or more countries the where condition can get very long!

CountryName = "Guatemala" or CountryName = "Honduras" or CountryName = "El Salvador" or "CountryName = "Nicaragua".

There is an easier way! Introducing the In() operator

The In() operator allows you to more easily specify a set of items. Here is the same expression using the In() operator. One thing to note is that you will have to type this into the SQL dialog box. The In() operator does not appear in the Operators list.

sql

This expression will achieve the same results but is a lot easier to set up.

Is there a limit to how many items I can list inside the In() operator?

There is a limit but it is not a hard coded number, rather the limit is related to how long an SQL statement can be. The In() operator has another advantage over trying to use a string of OR conditions in that it is much more efficient with space.

Is there more?

Sure! MapInfo Professional has a number of other keyword clauses you might find useful. In addition to In() there is Any(), All() and Between(). We'll cover these in future articles but if you are curious you can learn more in the MapInfo Professional Help. Look in the section on "Using Logical Operators in Expressions". A search on "Logical Operators" will turn this up.  

Article by Tom Probert, Editor of "The MapInfo Professional" journal

When not writing articles for "The MapInfo Professional", Tom enjoys talking to MapInfo Professional users at conferences and events. When not working he likes to see movies with car chases, explosions and kung-fu fighting.