topic Re: Definition Query Bug in ArcGIS Pro Questions

Definition Query Bug

BrianKaplan — Wed, 05 Feb 2020 17:38:43 GMT

I'm using ArcGIS Pro 2.4.3

I'm using the interactive Definition Query window as shown in the attached.

'Congo, Dem Rep of the' is not added correctly to the SQL string.

Below is the result in the SQL window The interactive window is incorrectly parsing 'Congo, Dem Rep of the' into two pieces.

COUNTRY_NA IN ('Angola', 'Congo', 'Dem Rep of the', 'Malawi', 'Tanzania', 'Zimbabwe', 'Mozambique', 'Congo', 'Dem Rep of the', 'Botswana', 'Namibia')

These data are from the US Department of State. http://geonode.state.gov/layers/geonode%3AGlobal_LSIB_Polygons_Detailed

Re: Definition Query Bug

Anonymous User — Wed, 05 Feb 2020 20:25:22 GMT

Hi Brian,

Does this include a comma in the name? I think this might be related to a bug we have logged.

BUG-000111347: The layer definition query with Add Clause and 'Include(s) the value(s)' on a text field with comma-separated single values is interpreted incorrectly and separates one value in the data into two separate values.

Re: Definition Query Bug

AndySiegel — Fri, 07 Feb 2020 19:11:04 GMT

I'm on 2.4.3 and also have this problem. I haven't upgraded to 2.5 yet, but does 2.5 provide the fix?

Re: Definition Query Bug

Anonymous User — Fri, 07 Feb 2020 20:51:15 GMT

Hi Andy, it doesn't look like this bug is supposed to be fixed in 2.5. There is a workaround, using delimiters other than commas.

Re: Definition Query Bug

AndySiegel — Fri, 07 Feb 2020 21:47:32 GMT

Hi Luba. Thanks for the response. What's an example of workaround using different delimiters, in the case mentioned by the OP?

Expected result:

COUNTRY_NA IN ('Angola', 'Congo, Dem Rep of the', 'Malawi', 'Tanzania', 'Zimbabwe', 'Mozambique', 'Congo', 'Dem Rep of the', 'Botswana', 'Namibia')

Actual result:

COUNTRY_NA IN ('Angola', 'Congo', 'Dem Rep of the', 'Malawi', 'Tanzania', 'Zimbabwe', 'Mozambique', 'Congo', 'Dem Rep of the', 'Botswana', 'Namibia')

Re: Definition Query Bug

Anonymous User — Fri, 07 Feb 2020 23:00:29 GMT

Hi Andy,

For example, instead of 'Congo, Dem of the' it would be 'Congo; Dem of the'-- or slashes. I haven't tested this out my end so I don't know what happens if the comma is removed completely and left with a space.

Re: Definition Query Bug

MarcoBoeringa — Sun, 09 Feb 2020 14:00:25 GMT

Luba Hristova‌, do you know what is the status of this bug, how is it classified in the ESRI bug tracking system?

I ran into something similar back in the days in Pro 1.0. This is really a *major* issue that needs fixing a.s.a.p. Things like this are a real PITA when using Pro, and should have top priority fixing. This is core functionality that should not fail.

Re: Definition Query Bug

Pål_Herman_Sund — Tue, 25 Feb 2020 10:32:46 GMT

Hi Luba Hristova - I received a supportcase from a Norwegian client with the exact same problem but related to "Select by Attributes".

As Andy Siegel pointed out the expected SQL is not produced by the "expression builder" - but it is possible to make a perfect SQL query by switching to directly writing SQL syntax (or edit the invalid result from the "expression builder").

I will raise a supportcase to add one more vote for fixing the "expression builder". In my case I have a text-field "opphav" with (some of the) possible values:

khm, kulturhistorisk museum, oslo
sametinget, hovedkontor
snåsa regionkontor, sametinget

Expression builder

Which gives 0 returns, due to the SQL being translated to:

opphav IN ('khm', 'kulturhistorisk museum', 'oslo', 'khm', 'kulturhistorisk museum', 'oslo', 'sametinget', 'hovedkontor', 'khm', 'kulturhistorisk museum', 'oslo', 'sametinget', 'hovedkontor', 'snåsa regionkontor', 'sametinget', 'khm', 'kulturhistorisk museum', 'oslo', 'snåsa regionkontor', 'sametinget', 'sametinget', 'hovedkontor')

But if I edit this SQL to (by switching to the SQL editor:

opphav IN ('khm, kulturhistorisk museum', 'sametinget, hovedkontor', 'snåsa regionkontor') I get the expected result

So no need to alter the data by replacing commas with other charachters, but indeed a need for making the expression builder produce correct SQL syntax.

Re: Definition Query Bug

Pål_Herman_Sund — Wed, 26 Feb 2020 08:09:05 GMT

Andy Siegel‌, Luba Hristova,‌ Marco, Brian

As this problem also affects "select by attributes" a raised a case last night and a new bug was accepted in minutes (thanks to Jacob Hartle for a prompt response).

Bug Number: BUG-000128909

Synopsis: In ArcGIS Pro, when using the Includes the value(s) or does not include the value(s) evaluators in the Select by Attributes tool, records fail to return if the field contains a comma.

Now it should be at least 3 bugs related to this problem in Pro, please fix

Re: Definition Query Bug

DanPatterson_Retired — Sat, 14 Mar 2020 22:57:31 GMT

BUG-000128909: When using 'includes the value(s)' or 'does not incl..

Strange, it says "not in current production plan"

but its 'duplicate' says Fixed in 2.6

BUG-000119957: In ArcGIS Pro, when using the Includes the value(s) ..

Re: Definition Query Bug

Pål_Herman_Sund — Sun, 15 Mar 2020 10:36:14 GMT

The latter bug (000119957) says fixed in 2.6. So my surprise was more "why not in upcoming servicepacks".

Re: Definition Query Bug

KoryKramer — Mon, 16 Mar 2020 12:46:37 GMT

It looks like it is being evaluated for inclusion in a 2.5 Patch 1.

Re: Definition Query Bug

JoshuaBixby — Mon, 16 Mar 2020 14:46:48 GMT

I am closing this question out as "Assumed Answered." I realize the bug hasn't been fixed yet, but the answer to the OP's question is, "it is a bug and Esri is working on addressing it." Kory Kramer‌, please keep posting updates on defect status so people that find this thread in the future has the latest information.