FGDB database view — Join fields missing from attribute table

[Bud]

ArcGIS Pro 3.2.1; file geodatabase

Background:

Here is a SQL query for creating a database view called species_records_latest_vw:

select
    *
from
    species_records
left outer join
    (select
        t_species,
        max(t_date) as t_date 
    from
        species_records 
    group by
        t_species) l
    on species_records.t_species = l.t_species 
       and species_records.t_date = l.t_date

Oracle 18c 10.7.1 EGDB:

If I try to create a view using the above SQL in Oracle, I'll get an error due to joined/duplicate column names. I won't be able to create the view. That's expected.

ORA-00957: duplicate column name

Mobile Geodatabase

I can create a view using the SQL in a Mobile Geodatabase without errors. The joined/duplicate columns are included in the attribute table. The duplicate fields are suffixed with ":1", making them unique. That's a valid way of handling the duplicate fields.

File Geodatabase

I can create a view using the SQL in a File Geodatabase without errors. However, the joined/duplicate columns have been excluded from the attribute table. That's not expected.

03528630 - ArcGIS Pro 3.2.1: FGDB database view has joined fields with duplicate names; joined fields should not be excluded from attribute table

BUG-000164420: Database View created in file geodatabase with Join definition does not create the required duplicate fields

Question:

The FGDB view should either behave like the Oracle view (throw an error) or behave like the Mobile GDB view (include the fields). But it doesn't do either of those things. It excludes the joined fields which defeats the purpose of the join and the view.

Are there any workarounds for this issue?

[Bud · Accepted solution]

Instead of using SELECT * for both tables at the same time, use a SELECT * for each table separately:

select
    s.*,
    l.*
from
    species_records s
left outer join
    (select
        t_species,
        max(t_date) as t_date 
    from
        species_records 
    group by
        t_species) l
    on s.t_species = l.t_species 
       and s.t_date = l.t_date

That seems to force the attribute table to use the table alias from the query as a prefix, making the field names unique in the attribute table.