Make Query Table — Use date field in join
ArcGIS Pro 3.2.1; file geodatabase
It's my understanding that date fields can't be used in joins between tables/FCs in the Make Query Table tool. The Key Field Options parameter excludes date fields from the pick list.
Could that be changed so that date fields are supported in joins? For example, join on SPECIES=SPECIES and DATE=DATE.
Related:
I've been having trouble with FGDB views. So I was hoping to use Make Query Table as an alternative.
Details:
Sample Excel data is attached to the original post above. It can be loaded into a file geodatabase.
I came across this issue with a FGDB view:
--latest_vw (I don't include these comments when creating a view.)
select t_species as v_species, max(t_date) as v_date from species_records group by t_species
Esri Case #03526583 - FGDB aggregation database view with SQL field aliases: Field name in attribute table is incorrect when underlying table is added to map
Esri Support said: "I understand that the attribute table of the database view behaves strangely and the V_SPECIES field will be shown as T_SPECIES when the SPECIES_RECORDS table is added to the map and I was able to reproduce the issue."
I had been assigning aliases/prefixes to field names (T_ for table, V_ for view) in an attempt to avoid confusing ArcGIS Pro about whether I was referring to fields in the underlying table vs. my database view. I don't actually know if that was a problem or not; I was just trying to eliminate possible issues. But that was causing the above bug, so I'll stop using aliases where possible.
New view:
--latest_vw
select t_species, max(t_date) as t_date from species_records group by t_species
That view works as expected in the attribute table:
I now have the latest records per species in LATEST_VW.
But that view is missing all the other fields from the original table. So I want to join from LATEST_VW to the SPECIES_RECORDS table on T_SPECIES=T_SPECIES and T_DATE=T_DATE.
So I'll make a "view on top of a view" to join LATEST_VW with SPECIES_RECORDS, joining on multiple fields. As far as I can tell, a "view on a view" is necessary because correlated subqueries aren't supported in FGDB SQL. I adapted the SQL from the screenshot in your (TanuHoque's) example.
--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
Problem:
The view doesn't return any records:
If I change the join type from INNER JOIN to LEFT OUTER JOIN, I can see that the join wasn't successful; it just produces nulls for the LATEST_VW fields.
--latest_with_other_fields_vw
select species_records.*, latest_vw.* from species_records left outer join latest_vw on species_records.t_species = latest_vw.t_species
and species_records.t_date = latest_vw.t_date
It should look like this (a parallel test in a mobile geodatabase):
(In the future, I could easily exclude the rows that aren't matches by using an INNER JOIN instead of a LEFT OUTER JOIN. Or in a definition query.)
My FGDB logic should work. I can prove it by converting LATEST_VW to a table called LATEST_TBL (copy the view in Catalog and paste it; it will be pasted as a table, not a view).
In a new view called LATEST_TBL_WITH_OTHER_FIELDS_VW, I'll join using LATEST_TBL instead of LATEST_VW.
--latest_tbl_with_other_fields_vw
select species_records.*, latest_tbl.* from species_records left outer join latest_tbl on species_records.t_species = latest_tbl.t_species and species_records.t_date = latest_tbl.t_date
That worked as expected. There are matches:
So I think my logic is sound. But making a "view on a view" seems to be a problem.
Anyway, I don't want to export the view as a table. I want to use live data in a view.
Other notes:
- I did the exact same tests in a mobile geodatabase but didn't have the issues mentioned above. Those issues only happen in a file geodatabase. That suggests there isn't a problem with my logic.
- I haven't found a way to "break the ties" when there are multiple records per species with the same date. At least, not when using FGDB SQL (whereas it can be done in mobile or enterprise geodatabases: Join — Control what related record gets used).
Summary:
As mentioned at the beginning of this reply, I was hoping to use Make Query Table as an alternative join mechanism (joining LATEST_VW to the SPECIES_RECORDS table) since FGDB SQL/views have limitations.
Edit:
I did a simpler test regarding "views on views". I created a simple SELECT * view on SPECIES_RECORDS called TEST_VW_1. Then I created an additional view called TEST_VW_2 that SELECTs * from TEST_VW_1. The attribute table of TEST_VW_2 is empty, which tells me that "views on views" don't work at all in FGDBs. So I think that's the root cause of my problem.