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?
The OP says that this operation used to work in ArcMap. Does this not appear to be a step backwards using Pro if you now need to create near duplicate fields that were not necessary in the past due to performance issues in Pro?
My take was that the general workflow used to work in ArcMap, not necessarily this query on this dataset. It would be good if the OP clarified.
It has worked on smaller datasets in the past. However, I just tried it with this dataset and it did not work. I was able to limit the search to a previous selection that was bounded by a rectangle, giving a fast processing time. However, I also change the test to "STATE"="MA", which should return all the parcels in the previous selection, and I get nothing. So I suspect its a more fundamental issue.
This content is coming in from AGOL, whereas before it came from a standalone ArcGIS Server. I'm starting to wonder if that's part of the issue here. I'm thinking I need to focus on the "STATE"="MA" issue first, before chasing the text vs. int issue.
All that said... I do see everything I should when I do an identify...
I've escalated this to a formal ESRI support request. I'll post the results!
Are all your TOT_VALUEs valid numbers (just stored as text)? Casting with AGOL-hosted stuff seems to work for me, but then I don't have any numbers stored as text to really test with!
I asked that earlier https://community.esri.com/message/790112-re-arcgis-pro-how-to-query-a-text-field-with-a-numeric-ope...
and apparently they are fine... which didn't make sense. There might be a non-castable string in the field
100000000+ conversions! o_O As I was reading this I was thinking "oh there's a few thousand or so rows." Wow, no wonder Pro is freaking out on it.
Yep, best plan is to copy that data to a number field.
suggested... apparently not in the cards for some reason
I like the scripting approaches shown here (nice work!) but I'm wondering if model builder might be a more efficient and easier process for Chuck to implement.
We'd use Safe FME on this which would be fairly trivial, but I'm guessing Chuck doesn't have access to that.
Progress: I needed to refer to TOT_VALUE inside quotes. But now it says the expression is invalid.
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
Failed to execute (SelectLayerByAttribute).
I thought that maybe the "WHERE" was not needed, tried it without that, same result.