Definition query on a query layer?

[Bud]

ArcGIS Pro 2.6.8; Oracle 18c 10.7.1 EGDB

I noticed recently that it's possible to create a definition query on a query layer:

I suppose that makes sense. All feature layers in the Contents pane have a definition query section in the layer properties, including query layers.

Out of curiosity, does anyone ever do that -- have a query layer that has a WHERE clause in the SQL definition, and have a definition query too? If so, what's the use case?

It might seem a bit strange to have WHERE clauses in both places. But I suppose definition queries are more convenient to modify than query layer SQL definitions, so maybe that's a reason to do it?

Thoughts?

[jcarlson · Accepted solution]

This is a definite edge case, but we have a database that pulls everything from OpenStreetMap for our county and keeps them in generalized tables (with an hstore column "tags" holding all the attributes).

If I'm doing something with transportation, I'll have a WHERE clause like

tags -> 'highway' is not null

 to get the routable linear features. But I may want to toggle whole categories of features on and off within that layer, so a definition query to focus on pedestrian or cycling features can be handy, so having a handful of definition queries makes it easy to flip between different sets.

To me, the WHERE clause is for a filter I don't ever mean to adjust, it's part of the layer's "definition". The definition query, though, is more like a "filter widget" to me, and is something I intend to turn on and off as needed.

Of course, using discrete parameters in the query can accomplish that, too, for simpler use cases.

[Bud]

Esri Case #03621411 - Invalid Syntax error when subquery used on unregistered table, query layer, or database view

BUG-000168914: Error "The SQL expression has invalid syntax" is encountered when a subquery is used on an unregistered table, query layer, or unregistered database view.

That issue applies to definition queries and Select By Attributes.

ArcGIS Pro 3.3.0; Oracle 18c 10.7.1 EGDB

table_non_reg_gdb.objectid IN (SELECT objectid FROM table_non_reg_gdb t2 WHERE t2.event_id = table_non_reg_gdb.event_id and fund_source =  'TOT' ORDER BY year_fund DESC FETCH FIRST ROW ONLY)