ArcGIS Pro: how to QUERY a text field with a numeric operator

8508
33
08-06-2018 06:59 AM
ChuckBenton
Occasional Contributor

I'm using an AGOL based feature layer that has all its features stored as text.  I want to use a query such as "Tot_Value >= 500000", however I get an error since Tot_Value is a text string.  I recall an earlier version of ArcMap allowed this type of operation using the same data source, so I believe it can be done.  I'm not a SQL expert, but assume I can cast this to an Int or similar, however a web search is coming up dry, and attempts on my part generate errors.  Suggestions? 

0 Kudos
33 Replies
RichardFairhurst
MVP Honored Contributor

You should try Cast(tot_value as float) > 500000 and cast(tot_value as int) > 500000. It is almost certainly the field type part of the argument that is throwing the error, since for a long time only float worked with fgdbs even though that type was not even in the documentation.  I just stumbled on it randomly.

0 Kudos
MichaelVolz
Esteemed Contributor

The Cast statement does not appear to be helpful as the OP still gets an error.

Chuck:

Have you tried working with ESRI technical support on this issue?

0 Kudos
FredCoud
New Contributor III

It worked perfectly fine for me, with ArcGIS Pro 2.1.3. Thanks !

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

You can do more with ArcPy than what the GUI will let you.  Since the ArcGIS relational data store is backed by PostgreSQL, try the following in the Python window:

arcpy.SelectLayerByAttribute_management(layer, "NEW_SELECTION", "CAST (Tot_Value as INTEGER) > 500000")
ChuckBenton
Occasional Contributor

Josh,  Thanks.  I've tried:

arcpy.SelectLayerByAttribute_management("PAAG_-_Feature_Layer\PAAG_-_Feature_Layer", "NEW_SELECTION", "WHERE CAST (TOT_VALUE as INTEGER) > 500000"")

but get

Parsing error
SyntaxError: EOL while scanning string literal (<string>, line 1).

I started with the layer not in quotes (which is how I think it should be)... same error.

Suggestion as to what I'm doing wrong?

Chuck

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

You have an extra quotes in the ArcPy code you pasted, which would generate the error you see.  Trying copying and pasting this:

arcpy.SelectLayerByAttribute_management("PAAG_-_Feature_Layer\PAAG_-_Feature_Layer", "NEW_SELECTION", "WHERE CAST (TOT_VALUE as INTEGER) > 500000")

0 Kudos
ChuckBenton
Occasional Contributor

Thanks again.  I have a new error now, see below: 

arcpy.SelectLayerByAttribute_management("PAAG_-_Feature_Layer\PAAG_-_Feature_Layer", "NEW_SELECTION", "WHERE CAST (TOT_VALUE as INTEGER) > 500000")
Runtime error
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 6314, in SelectLayerByAttribute
raise e
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\management.py", line 6311, in SelectLayerByAttribute
retval = convertArcObjectToPythonObject(gp.SelectLayerByAttribute_management(*gp_fixargs((in_layer_or_view, selection_type, where_clause, invert_where_clause), True)))
File "c:\program files\arcgis\pro\Resources\arcpy\arcpy\geoprocessing\_base.py", line 506, in <lambda>
return lambda *args: val(*gp_fixargs(args, True))
arcgisscripting.ExecuteError: ERROR 000358: Invalid expression
ERROR: code:504, Your request has timed out., Proxy server got bad address from remote server (verify the server is running).
Failed to execute (SelectLayerByAttribute).

While I still have an "Invalid Expression" error, I also now have a time out.  I think this may be because I'm querying a huge (100+M records) layer.  I'm also trying to figure out how to limit my query to only the visible extent of the map.  I started another request here: https://community.esri.com/message/790143-arcgis-pro-how-to-limit-select-by-attribute-to-the-visible... 

Thoughts?

0 Kudos
MichaelVolz
Esteemed Contributor

How about applying a definition query to the layer to limit it to just 10,000 or 100,000 records just to see if the time out error goes away?

0 Kudos
ChuckBenton
Occasional Contributor

In the other thread it was suggested I use "Select from Current Selection", which makes sense.  In this case, I've substituted "SUBSET_SELECTION" for "NEW_SELECTION", and made a rectangular selection of a small area before running the script.  Same sort of delay, but I didn't see the Code 504 error.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Casting a field will not allow the index to be used, if there is one, so you are basically doing a full-table scan on 100+M records.  It isn't surprising it is timing out.  Getting back to what Dan has mentioned, you really need to convert the field to integer and then index it.