Make query table error

5729
15
06-28-2016 04:45 AM
AlonsoDiego
New Contributor II

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

Reply
0 Kudos
15 Replies
WesMiller
Regular Contributor III

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

Reply
0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Do you have the bug number?

Reply
0 Kudos
WesMiller
Regular Contributor III

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

JoshuaBixby
MVP Esteemed Contributor

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

JoshuaBixby
MVP Esteemed Contributor

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..

Reply
0 Kudos
AlonsoDiego
New Contributor II

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!