topic Bug: EGDB view — YEAR column displayed incorrectly as zeros in attribute table in ArcGIS Pro Questions

Bug: EGDB view — YEAR column displayed incorrectly as zeros in attribute table

Bud — Thu, 26 Jan 2023 06:12:13 GMT

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.

Re: EGDB view — YEAR column displayed incorrectly as zeros in attribute table

Bud — Wed, 25 Jan 2023 21:33:31 GMT

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?

Re: EGDB view — YEAR column displayed incorrectly as zeros in attribute table

Asrujit_SenGupta — Thu, 26 Jan 2023 14:52:05 GMT

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.

Re: Bug: EGDB view — YEAR column displayed incorrectly as zeros in attribute table

Bud — Wed, 01 Feb 2023 15:41:00 GMT

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=v2&px=400
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.