Make Query Table — Use date field in join

Bud — Sat, 13 Jan 2024 18:56:51 GMT

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.

Re: Make Query Table — Use date field in join - Status changed to: Needs Clarification

TanuHoque — Fri, 12 Jan 2024 01:51:02 GMT

@Bud

did you happen to try Create Database View and see if that work? I did a quick test with some made up datasets and it worked for me (fingers crossed).

as you know this tool allows you to use sql statements to create a view inside a file gdb.

Re: Make Query Table — Use date field in join

Bud — Mon, 15 Jan 2024 07:26:15 GMT

@TanuHoque

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.

Re: Make Query Table — Use date field in join - Status changed to: Open

TanuHoque — Sat, 13 Jan 2024 18:55:48 GMT

Thanks @Bud for providing detail clarification. We appreciate this very much. I have changed it back to Open status. Also changing the label to GeoDatabase.

idea Make Query Table — Use date field in join in ArcGIS Pro Ideas

Make Query Table — Use date field in join

Re: Make Query Table — Use date field in join - Status changed to: Needs Clarification

Re: Make Query Table — Use date field in join

Re: Make Query Table — Use date field in join - Status changed to: Open