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)
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?
Always had the understanding that ArcSDE\Enterprise gdb does not have reserved words of its own. These are determined by the host RDBMS on which the geodatabase resides.
This document for ArcGIS Pro mentions the same: Define fields in tables
Additional field name rules and limitations are as follows:
- Field names cannot contain reserved words, such as all or result.
Consult your database management system (DBMS) documentation for additional reserved words.
I've been doing some testing with Esri Canada support (Case #03252485). Here's where I'm at:
Unfortunately, I’m currently unable to reproduce the issue – not even with my original view – the one I used to take the screenshot that I put in the Esri Community post: https://community.esri.com/t5/image/serverpage/image-id/61266i16BFB9CD1AF2C02E/image-size/medium?v=v...
I’m not sure why I’m not seeing the issue right now. The years (field name: YEAR) in the view are being displayed as numbers, not zero, as desired.
It’s frustrating that the issue is intermittent/inconsistent.
I’m going to do some more testing.