CAST does not work in SearchCursor Queries?

dougbrowning

I thought a found a slick way to compare on a number in a string field. 

CAST(SoilDepthLower AS INTEGER) < 70

This works in a ArcMap definition query just fine. 

But when I try to use it in a SearchCursor Query it says Invalid SQL Statement.

                    whereClause = "CAST(SoilDepthLower AS INTEGER) < 70"
                    keyField1 = arcpy.ListFields(tableLayer)[1].name
                    keyField2 = arcpy.ListFields(tableLayer)[2].name
                    fieldList = ["SoilDepthLower", keyField1, keyField2]
                    with arcpy.da.SearchCursor(tableLayer, fieldList, whereClause) as cursor:

I have tried smallint, double, etc - using both uppercase and lower case.

This help page says it is supported  http://pro.arcgis.com/en/pro-app/help/mapping/navigation/sql-reference-for-elements-used-in-query-expressions.htm

What is the issue?

Thanks

dougbrowning

Nevermind it does work for me when hitting a GDB directly.  So it must be a limitation with in_memory.

Weird because I used the Feature Class to Feature Class tool to create it.  Then I have 60+ other queries in this script and they all work just fine.  Some are huge too.  Only the Cast does not work.

I did find this post where Esri said only basic stuff.  Weird limitation https://community.esri.com/thread/165846

I already had put a workaround by doing a Add Field and Calculate Field since it is just a in_memory table it does not change the original.

Thanks