topic Re: CAST does not work in SearchCursor Queries? in Python Questions

CAST does not work in SearchCursor Queries?

DougBrowning — Tue, 17 Jul 2018 21:39:14 GMT

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

Re: CAST does not work in SearchCursor Queries?

DanPatterson_Retired — Sat, 11 Dec 2021 12:57:06 GMT

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'‍‍‍‍‍‍‍

Re: CAST does not work in SearchCursor Queries?

JoshuaBixby — Wed, 18 Jul 2018 13:54:59 GMT

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 "*" ?

Re: CAST does not work in SearchCursor Queries?

DougBrowning — Wed, 18 Jul 2018 14:27:40 GMT

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

Re: CAST does not work in SearchCursor Queries?

TedKowal — Wed, 18 Jul 2018 16:09:53 GMT

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

Re: CAST does not work in SearchCursor Queries?

DougBrowning — Wed, 18 Jul 2018 16:52:59 GMT

It is a python in_memory table (not a FC) which in my experience acts just like a GDB. When I make a test copy into a GDB and use my cast query in a Definition Query it works great. Which should simulate the interface directly.

I have tried int, INT, Interger, INTEGER, SHORTINT, and a few others.

thanks

Re: CAST does not work in SearchCursor Queries?

DougBrowning — Wed, 18 Jul 2018 16:53:31 GMT

Can you test it on a in_memory table using 10.6?

Re: CAST does not work in SearchCursor Queries?

JoshuaBixby — Wed, 18 Jul 2018 16:54:48 GMT

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.

Re: CAST does not work in SearchCursor Queries?

DougBrowning — Wed, 18 Jul 2018 17:03:13 GMT

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

Re: CAST does not work in SearchCursor Queries?

JoshuaBixby — Wed, 18 Jul 2018 17:08:13 GMT

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.

Re: CAST does not work in SearchCursor Queries?

JoshuaBixby — Wed, 18 Jul 2018 17:23:13 GMT

If you feel one of these responses answered your question, please mark it so to close out the question. Otherwise, I will close it as "assumed answered" later.

Re: CAST does not work in SearchCursor Queries?

DougBrowning — Wed, 18 Jul 2018 17:28:50 GMT

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