ArcGIS Pro 3.2.1; File Geodatabase:
It would help to be able to create a view on top of an existing view in a file geodatabase.
Currently, in simple cases like SELECT * FROM EXISTING_VIEW, the resulting view has zero rows (incorrect). Whereas in all other geodatabase and database types, a view can select from an existing view without issue.
Use Case:
1. Organization. Break large queries into smaller, more manageable pieces.
2. Also, complex queries like greatest 1 per group (with ties). See: Selecting the most recent records based on unique values in another field and my comment about FGDB view challenges.
For example, get the latest row per species group (with ties), and include all the fields from the original table. That can't be done in a single FGDB view due to various FGDB SQL limitations (update: see follow-up reply below). Instead, I want to use multiple stacked views.
- A view that gets the latest row per group (but doesn't include the other fields from the original table):
--latest_vw
select
t_species,
max(t_date) as t_date
from
species_records
group by
t_species
- And then a view on top of that view that takes the fields from the original table and joins to LATEST_VW. The join is based on multiple fields: T_SPECIES=T_SPECIES and T_DATE=T_DATE.
--latest_with_other_fields_vw
select
species_records.*,
latest_vw.*
from
species_records
inner join
latest_vw
on species_records.t_species = latest_vw.t_species
and species_records.t_date = latest_vw.t_date
- That approach works in mobile and enterprise geodatabases but not in file geodatabases since "a view on a view" doesn't seem to be supported; the joined fields from LATEST_VW are completely null, which is incorrect.
If I could create a view on a view, then that would solve the greatest 1 per group (with ties) problem in FGDB SQL.
I've attached sample Excel data that can be loaded into a file geodatabase.