Overcoming the Make Query Table bug in ArcGIS

According to my notes, I first used the Make Query Table tool in my first week at Aurecon, back in March 2012. It was the first of many, many times, because often when receiving spatial data in a non-spatial format from a non-GIS user, the first thing that gets thrown out is any trace of the original spatial component.

At some point, I realised the tool’s expression parameter was a bit wonky. As I have come up against this problem every few months since (forgetting it happens each time because I only thought to write down a note about it now), I have decided to immortalise it in a gist below.

http://desktop.arcgis.com/en/arcmap/10.3/tools/data-management-toolbox/make-query-table.htm

When inputting the optional SQL clause, ArcGIS automatically adds quotation marks “” to the field names in the dialog box. This will
pass the tool’s error checking successfully but will cause the tool to fail with an error.

If you verify the SQL clause in the dialog box, it will give a SQL error with no specifics. When adding the clause, remember to remove
the quotation marks.

e.g. If you want to join Layer1 to Layer 2 on common field ID and where Layer 1 contains “Cape Town”, ArcGIS will format your expression
in the following way:

"Layer1.ID" = "Layer2.ID" AND "Layer1.TOWN" = 'Cape Town'

You need to change it to

Layer1.ID = Layer2.ID AND Layer1.TOWN = 'Cape Town'

view raw
makequerytablebug.md
hosted with ❤ by GitHub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.