topic Re: create query table doesn't work in a model in Data Management Questions

create query table doesn't work in a model

AntoniaCreese — Wed, 07 Sep 2016 07:11:16 GMT

I need to use the create query table tool. When I do it manually it works fine but will NOT work in a model.

I have both table and spatial layer in athe same personal geodatabase. I have tried using different syntax, double quotes, single quotes, square brackets around table and field expression. All deliver empty results and error 1.

Re: create query table doesn't work in a model

DanPatterson_Retired — Wed, 07 Sep 2016 07:13:40 GMT

Why don't you do it manually and copy the syntax used from the Results window?

Re: create query table doesn't work in a model

AntoniaCreese — Wed, 07 Sep 2016 07:27:15 GMT

Thanks Dan,

I have tried that and the expression and create table works manually outside of the model window but not within it. Same expression, same inputs.

Re: create query table doesn't work in a model

DanPatterson_Retired — Wed, 07 Sep 2016 10:15:19 GMT

it probably has something to do with the input path names. Are there any spaces and other characters in the paths? any non-simple characters (internationalization)? Anything you can provide as to the inputs both through the common interface and through the model would be useful. Also, check your environment settings, I doubt you have provided any within the model, but there may be some being used through the tool

Re: create query table doesn't work in a model

BruceHarold — Wed, 07 Sep 2016 14:43:58 GMT

Hi, it is a known issue that the expression in Make Query Table may not validate when it is correct and vice versa.

For File GDB inputs build the expression without double quotes.

Re: create query table doesn't work in a model

DanPatterson_Retired — Wed, 07 Sep 2016 14:49:18 GMT

even in 10.4.1? Is there a bug number? when was the error introduced? Times like this, I wish they would like bugs/issues to the help files

Re: create query table doesn't work in a model

Robert_LeClair — Wed, 07 Sep 2016 15:00:12 GMT

By chance are you running the Table to Table GP tool prior to the Make Query Table GP tool in ModelBuilder? If so, this is a known bug - BUG-000088138 : The 'Table to Table' tool fails in ModelBuilder if the input is a query layer.

Re: create query table doesn't work in a model

AlanRex1 — Wed, 07 Sep 2016 18:20:11 GMT

Yes, the bug Bruce mentioned was logged for 10.4.1 (although I think it may have existed at 10.4 as well):

BUG-000097508 - The Query Builder of the Make Query Table geoprocessing tool uses inconsistent delimiter syntax, returns inconsistent errors, and leads to inconsistent results in ArcGIS 10.4.1 for Desktop.

The bug is not directly related to ModelBuilder, but it may be causing some extra confusion in this case. Since the tool is working outside of ModelBuilder I suspect that the bug Robert mentions below might be the main problem. BUG-000097508 is related to various incorrect errors which are thrown when validating syntax generated by the Query Builder. While it's possible that Antonia is hitting some of these incorrect errors, if she's got it working in the standalone tool then it should also work in ModelBuilder.

Re: create query table doesn't work in a model

WesMiller — Wed, 07 Sep 2016 18:28:56 GMT

That bug has been around a long while

Re: create query table doesn't work in a model

AntoniaCreese — Thu, 08 Sep 2016 00:09:05 GMT

No spaces or characters. I have found [layer].[field] = [tableName].[field] with both fields sharing the same data format in this case text and even the same size works well outside the model. I will check environment settings but suspect as a later comment suggests it is an esri "feature" aka bug.

Re: create query table doesn't work in a model

AntoniaCreese — Thu, 08 Sep 2016 00:10:46 GMT

no not running table to table tool. The table is exported from database.

Re: create query table doesn't work in a model

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

I will believe it when I see it, but the status was recently updated to say it will be addressed in 10.5.1:

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

Re: create query table doesn't work in a model

GarretDuffy — Sun, 15 May 2022 17:49:18 GMT

Verifying this is still a bad bug. Make Query Table works joining two tables together outside of Model Builder but once it's replicated within Model Builder, I get ERROR 000152, Invalid Input Data. Any suggestions/workarounds gratefully received.