topic Re: Make query table error in Data Management Questions

Make query table error

AlonsoDiego — Tue, 28 Jun 2016 11:45:25 GMT

Hi,

I need to join one feature class and one table. This is a one to multiple join, therefore, my only option seems to be by creating a query table. My problem is that the fields I want to join do not seem to be matched properly when I run the query table tool. I've tried using the following expression in the query table expression field to indicate that these are the fields to be joined:

("Infraestructura.idInfraestructura" = "InfraestructuraServicio.idInfraestructura") AND ("InfraestructuraServicio.idServicio" = "Servicio.idServicio")

But the layer result is empty.

I think I may be doing something wrong (perhaps the problem is with my query), but I'm not sure what. When I verify my query in the query builder window, I keep getting a message saying

The expression was verified successfully but no records were returned

which does not make any sense as there should definitely be some matches.

Thanks

Re: Make query table error

JoshuaBixby — Tue, 28 Jun 2016 13:54:06 GMT

If you would "Copy As Python Snippet" and paste the resulting ArcPy code here, it might help people reply since they would have more of the arguments.

Have you tried using just one or the other condition? Do you get results from doing both alone?

Re: Make query table error

WesMiller — Tue, 28 Jun 2016 16:17:50 GMT

At 10.2 Make query table has a bug. Not sure what version your working with, but try removing the quotes from your field names.

Re: Make query table error

AlonsoDiego — Tue, 28 Jun 2016 17:15:41 GMT

Hi Wes,

I'm working with 10.3 version of ArcGIS.... I try removing the quotes but the table don´t have any records too...

Thanks!

Re: Make query table error

AlonsoDiego — Tue, 28 Jun 2016 17:18:21 GMT

Hi Joshua,

Here I post the code:

... # Import arcpy module

... import arcpy

...

... # Local variables:

... Infraestructura = "Infraestructura"

... InfraestructuraServicio = "InfraestructuraServicio"

... Servicio = "Servicio"

... query2 = "query2"

...

... # Process: Make Query Table

... arcpy.MakeQueryTable_management("Infraestructura;InfraestructuraServicio;Servicio", query2, "ADD_VIRTUAL_KEY_FIELD", "", "Infraestructura.Shape #;Infraestructura.idInfraestructura #;Servicio.idServicio #", "Infraestructura.idInfraestructura = InfraestructuraServicio.idInfraestructura AND Servicio.idServicio = InfraestructuraServicio.idServicio"

Thanks!

Re: Make query table error

JoshuaBixby — Tue, 28 Jun 2016 18:06:35 GMT

I see what you are trying to do, and it generally seems right. Could you sanitize some subsets of data and post them?

Getting back to the other question I asked earlier. What happens if you put only one condition in at a time? Results, no results?

Re: Make query table error

AlonsoDiego — Wed, 29 Jun 2016 05:41:01 GMT

Hi Joshua,

I uploaded my geodatabase to dropbox, here is the link: https://dl.dropboxusercontent.com/u/108321176/Infraestructura.mdb

When I put only one condition the results are wrong...

I hope someone can help me with this...

Thanks!

Re: Make query table error

JoshuaBixby — Wed, 29 Jun 2016 15:27:41 GMT

It is good you uploaded data, there are several important aspects that weren't described originally.

The data is in a personal geodatabase
- Although most tools still support working with personal geodatabases, it is always good to disclose what type of back-end storage is being used, especially shape files and personal geodatabases. As versions march on, the number of quirks with shape files and personal geodatabases grow.
InfraestructuraServicio is a relationship class
- Although the Make Query Table documentation doesn't explicitly state that relationship classes are not supported, it also doesn't say they are supported. All of the language in the documentation talks about "tables."
Infraestructura is in a feature dataset while the other object/tables are in the root-level of the geodatabase.
- According to the Make Query Table documentation, "All input feature classes or tables must be from the same input workspace." I don't have a link to the documentation on-hand, but I believe feature datasets are considered workspaces in and of themselves.
You are using field aliases that differ from actual field names and they have spaces in them.
- I don't know whether this is affecting anything, but it is something I typically don't work with, especially field aliases with spaces in the name.

I tried a handful of changes here and there, but I kept generating different error messages. I don't know what factors above are playing into the issue you are having. I would start by creating a file geodatabase and working with 3 tables (no relationship classes) with no field aliases in the root-level of the geodatabase. If that works, start adding back in aspects like field aliases or move data to personal geodatabase. At some point, you will stumble on what is breaking.

UPDATE: It appears the tool will work with the data as-is, if the quotes are removed from the expression. Furthermore, there seems to be a bug in the expression builder that generates an error when the quotes are removed and the expression is tested, but yet the expression still works

Re: Make query table error

WesMiller — Wed, 29 Jun 2016 15:56:54 GMT

I got it to work in 10.2 this way.

Infraestructura.idInfraestructura = InfraestructuraServicio.idInfraestructura AND Servicio.idServicio = InfraestructuraServicio.idServicio

Re: Make query table error

JoshuaBixby — Wed, 29 Jun 2016 16:06:54 GMT

Interesting. I am running 10.4.1 and got your syntax to work. Why I didn't think it would work when I tried earlier was that I was using the expression builder to validate expressions and it generates an error: "There was an error with the expression. General function failure [Infraestructura] Too few parameters. Expected 3."

So, it seems to work, but there is a bug in the expression builder that says the expression isn't valid when tested.

Re: Make query table error

WesMiller — Wed, 29 Jun 2016 17:30:03 GMT

Joshua I spent a long time on the phone with ESRI on this issue. It is a known bug.

Re: Make query table error

JoshuaBixby — Wed, 29 Jun 2016 17:32:26 GMT

Do you have the bug number?

Re: Make query table error

WesMiller — Wed, 29 Jun 2016 17:39:41 GMT

Sorry no

See these posts no bug number but it appears the bug has been around awhile

A Quick Tip on Performing a 1:M Join | Support Services Blog

A quick tip on performing a 1:M join MQT2 – Support Services Blog

Re: Make query table error

AlonsoDiego — Thu, 30 Jun 2016 08:43:07 GMT

Hello everyone,

At the end I did it!
In Key Field Options I selected USE_KEY_FIELDS, but then I don't indicated any of them in the table below. It seems that ArcGIS is taken by default or through the expression.
In this way, the tool has worked ......

Thanks to everyone!

Re: Make query table error

JoshuaBixby — Thu, 30 Jun 2016 16:29:56 GMT

Poked around a bit, it turns out a related issue has been around since ArcGIS 9.2!

Bug NIM048170
Synopsis
Make Query Table Tool SQL Query Validation throws an error on valid query expressions when the expression contains a statement to set field in one table equal to a field in a second table.

Supposedly it is "In Product Plan," they are just moving rrreeeaaallllyyyy ssslllooowwwlllyyy. I guess since ignoring the error works, they figure they have time.

Funny enough, a very similar bug was logged with ArcGIS 10.0!

Bug NIM096505
Synopsis
The SQL query validation in the Make Query Table tool displays an error on valid query expressions.

Oddly, this bug is still "New," which I guess makes some sense given they are still working on fixing the same bug in 9.2.

The problem, or a similar one, was logged again at ArcGIS 10.2.2. The response is such a doozy I have to include it all.

Bug NIM101930
Synopsis
The Make Query Table tool does not correctly process data from file geodatabases.
Additional Information
This works correctly in the current development build. Note that the quotes around the field names in the join expression must be removed for this to work. So "FeatureClass.FeatureID" = "Table.TableID" must be FeatureClass.FeatureID = Table.TableID
Alternate Solution
1. Migrate the data to a personal geodatabase. 2. Make sure to adjust the syntax of the expression to brackets.

The status is "Not in Current Product Plan," which is a bit confusing to me, but maybe they don't plan on addressing it because it is already working in the "current development build," although we have no idea what build that is. My favorite suggestion is to copy data to a personal geodatabase, which we know the expression builder doesn't build valid expressions for. The user could put the brackets in themselves, just don't try to verify the syntax because you will get a bogus error message.

Because the issue still exists, and seems to be larger than just validating syntax, I opened another bug with Esri. Who knows, maybe fourth time will be a charm.

#BUG-000097508
The Query expression builder in the Make Query Table geoprocessing tool uses inconsistent delimiter syntax, throws inconsistent errors and leads to inconsistent results in 10.4.1

Re: Make query table error

JoshuaBixby — Tue, 13 Dec 2016 20:13:49 GMT

Update, update, hear all about it! Supposedly, this matter will finally be put to rest with ArcGIS 10.5.1:

BUG-000097508: The Query Builder of the Make Query Table geoprocess..