What SQL commands do in_memory workspaces support?

[RyanCoodey]

In_memory workspaces are great to work with, especially when needing temporary/intermediate steps in an ArcPy script. Currently I am using an arcpy.da.SearchCursor against one and using simple where_clause statements with operators (>, <, 😃 seem to work just fine. But trying to take it a bit further and use a sql_clause to prefix with a 'DISTINCT' or postfix with a 'ORDER BY' and it doesn't seem to obey those.

Is there any documentation that says what SQL is supported by these in_memory workspaces?

Thanks a lot for any help and information!

[VinceAngelo]

Keep in mind that there isn't a SQL database backing an in_memory workspace.  I wouldn't expect any complex SQL statements to work, and I'd be leery of trying to use even basic functions (like length).  File geodatabase attempts to emulate some of the SQL language, but shapefiles and in_memory feature classes don't have more than simple WHERE expression operators.

- V

[DougBrowning2]

I have a script with 60+ queries in it against a in_memory table.  They all word great except when using CAST.  It will work on a GDB directly but in_memory gives a invalid SQL statement error.

Lots of documentation shows using in_memory interchangeably in many arc tools.  You think these limits would come up.  

Ideally change it to make it work just like a GDB.  If not please at least document these limitations.  This hunt and pecking to see what works costs lots of time.  Esp when you have no idea why since it does not say anywhere that in_memory is limited.

Please post any list if it does exist.

Thanks

[curtvprice]

I posted a request to update this doc page to talk about SQL limitations for in_memory

SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop 

[JoshuaBixby]

I posted a request ....

An ArcGIS/GeoNet Idea or some other request?  If the latter, what and where?

[curtvprice]

Hi Joshua.

Every ArcGIS help article has a link at the bottom: Feedback on this topic?

I have it on good authority these comments get forwarded directly to the person responsible for that help article at Esri. So I often take advantage of this to send a note directly to that page author when I find something that I find confusing or incomplete, (less commonly) wrong.

[JoshuaBixby]

Got it.  I do the same sometimes, but I was also wondering if you created an Idea or logged an enhancement request as well.  Although I agree the Feedback link does seem to get to the appropriate audience in Esri, I don't like the black box that it becomes.  Occasionally I start an e-mail dialogue with a Product Lead after submitting Feedback, but there is no formal structure for you to track status.