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

[ChuckBenton]

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? 

[MichaelVolz]

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?

[JoshuaBixby]

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.

[ChuckBenton]

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

[ChuckBenton]

I've escalated this to a formal ESRI support request.  I'll post the results! 

[PeteCrosier]

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!

[DanPatterson_Retired]

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 

[AndrewQuee]

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.

[DanPatterson_Retired]

suggested... apparently not in the cards for some reason

[AndrewQuee]

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.

[ChuckBenton]

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.