Bug: EGDB view — YEAR column displayed incorrectly as zeros in attribute table
ArcGIS Pro 2.6.8
I have an Oracle 18c 10.7.1 EGDB standalone table:
YEAR AMOUNT ---------- ---------- 2024 100 2025 200 2025 300 2026 400 2027 500 2028 600 2028 700 2028 800 2029 900 2031 100
I've created a view on the table that generates filler rows:
--create or replace view a_test_tbl_vw as
select
cast(rownum as number(38,0)) as rownum_,
cast(year as number(38,0)) as year,
amount
from
(
select
y.year,
t.amount
from
(select extract(year from sysdate) + level - 1 as year from dual connect by level <= 10) y
left join
infrastr.a_test_tbl t
on t.year = y.year
order by
y.year,
t.amount
)
ROWNUM_ YEAR AMOUNT
---------- ---------- ----------
1 2023
2 2024 100
3 2025 200
4 2025 300
5 2026 400
6 2027 500
7 2028 600
8 2028 700
9 2028 800
10 2029 900
11 2030
12 2031 100
13 2032 Use Case: Force bar chart to show missing years within 10-year range (system year + 9)
SQL Source: Oracle: Generate rows for missing years within range (sysyear + 9)
Problem:
When I add the view to ArcGIS Pro 2.6.8, the YEAR column is displayed as zeros, which is incorrect:
Whereas, ArcMap 10.7.1 displays the YEAR integers correctly:
Why is the YEAR column being displayed as zeros in ArcGIS Pro?
I'm aware that views can be tricky in ArcGIS, especially when there are generated rows — rows that aren't selected directly from a table.
But I believe I've satisfied all of ArcGIS' requirements for views. The view has a unique ID that is a number. And the YEAR column has been CAST as a proper integer too. So I think it should work. And the fact that it does work in ArcMap tells me that it should work in ArcGIS Pro too.
The problem seems to be the column name "YEAR".
If I change that column name to something else, such as "YEAR_", then it works fine in ArcGIS Pro:
--create or replace view a_test_tbl_vw as
select
cast(rownum as number(38,0)) as rownum_,
cast(year as number(38,0)) as year_,
amount
from
(
select
y.year,
t.amount
from
(select extract(year from sysdate) + level - 1 as year from dual connect by level <= 10) y
left join
infrastr.a_test_tbl t
on t.year = y.year
order by
y.year,
t.amount
)
"YEAR" is an SQL keyword in Oracle (but using it in SQL isn't prohibited, just discouraged). Maybe that causes a problem in ArcGIS Pro? Or maybe "YEAR" is a reserve word in Pro, but not in ArcMap?
