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

What is the issue?

Thanks

[DanPatterson_Retired]

cast replacements can be replace somewhat with 'isinstance' but normally you would have to use a full def to check for contents of the field, but this will give you an idea.  You would have to add the quote thingys to make it a query string, but you can see the idea for a check

a = ["40", 30, "20", 10, '1']

for i in a:
    print("{} < 70".format([i, int(i)][isinstance(i, str)]))
    
40 < 70
30 < 70
20 < 70
10 < 70
1 < 70‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

so 'i' in the above would simply be the fieldname (in ! ! )and you would skip the print of course.  There a lots of cavaets to this however.

For example

int("1.23")
Traceback (most recent call last):

  File "<ipython-input-24-84a3541b8dc8>", line 1, in <module>
    int("1.23")

ValueError: invalid literal for int() with base 10: '1.23'‍‍‍‍‍‍‍

[JoshuaBixby]

What version of ArcMap are you using?  I just created a search cursor in ArcMap 10.6 and ArcGIS Pro 2.2 on file geodatabase table using CAST in the WHERE clause, and it worked with no errors.

What happens if you change the fields from fieldList to "*" ?

[DougBrowning]

10.5.1  I tried the * and still get Invalid SQL statement.  I also tried different queries before to make sure the code was right and it worked.  It is just the cast that gives the error.

thanks

[DougBrowning]

Can you test it on a in_memory table using 10.6?

[JoshuaBixby]

I can, might not be able to get around to it for a few hours.  That said, in-memory workspaces are not file geodatabases and don't support all the geodatabase functionality, including support of SQL.

[DougBrowning · Accepted solution]

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

[JoshuaBixby]

Honestly, I have never seen documentation on what limitations the in-memory workspace has, just that it doesn't fully implement the geodatabase model.  Why does X SQL statement work but not CAST?  I have no idea.

[DougBrowning]

Yea I can understand limitations but then say that in the doc.  It should really be a list of what works and does not but at least mention that there are some.  Why hide this info?  It can cost people lots of time.  Esp when so much of the help files use in_memory so interchangeably.

Anyways the reason I said it works like a GDB is based on experience with shp files.  If you copy a shp file to a in_memory space it will then act like a GDB.  One example is creating a new int field.  In a shp file it defaults the field to 0 but in a GDB it defaults to Null.  (This tripped up a script I was speeding up for someone that was counting on the 0.)

Thanks

[TedKowal]

Try your query using your Database interface directly to test the where clause.  CAST is a database specific function -- I know SQL Server can used it; however; an error will occur if run against an MS Access database for that you would have to use INT(....). -- as an example --