topic Re: ArcGIS Pro: use layer name in SQL in ArcGIS Pro Questions

ArcGIS Pro: use layer name in SQL

Greg_Yetman — Wed, 15 Mar 2017 21:45:10 GMT

I'm trying to select records using "Select Layer by Attribute" using a subquery. My input to the query is a layer (review_records) in the project that has a definition query applied (field "Assessment" is null); the source is a File Geodatabase feature class. I can do simple queries using the edit clause mode without any problems. However, when I try and use SQL with a subquery I cannot reference the layer name, only the underlying feature class name. This statement:

OBJECTID = (SELECT MIN(OBJECTID) FROM review_records)

won't validate. The same statement referencing the feature class name validates fine:

OBJECTID = (SELECT MIN(OBJECTID) FROM ben_fp)

but it does not return any selected records as the minimum OBJECTID value does not meet the condition set in the definition query. If I change MIN to MAX one record is selected as I would expect (the max objectid record has NULL for the assessment field).

I feel like I'm missing something obvious--is there a better way to do this (other than creating a new feature class that only has NULL assessment values)?

Greg

Re: ArcGIS Pro: use layer name in SQL

Robert_LeClair — Wed, 15 Mar 2017 22:27:55 GMT

Greg - not sure if this will help you or not but it's a start - http://pro.arcgis.com/en/pro-app/help/mapping/navigation/sql-reference-for-elements-used-in-query-expressions.htm#GUID-F5320DB5-4E54-4D49-BF31-A58F5379ED9F

Re: ArcGIS Pro: use layer name in SQL

Greg_Yetman — Thu, 16 Mar 2017 02:28:33 GMT

Thanks Robert. That's the documentation I used to construct my subquery. I noticed that it says that subqueries are "...supported by geodatabase data sources only". I had assumed that this included layers based on data in a Geodatabase but I guess that subqueries are only supported on the feature classes and tables in a Geodatabase, not layers or table views.

I guess that I'll have to find a workaround. This is for a work task in Pro that I'm developing that will be used repeatedly, so creating a new feature class is not a good option.