Data not displaying correctly in Attribute Table (SQLite)

Question

I have a layer that is connected to a feature class stored in a SQLite database. If I open the Attribute Table for this layer, the ROUTE_TYPE field is set to 0 for every row.

However, I know that there are actually no rows in this dataset that have ROUTE_TYPE = 0. If I open the Table for the source feature class directly in the Catalog pane, all rows are non-zero (in this case the top 8 rows have value '1'.

I have used an external SQLite browser tool and confirmed this field only takes unique values 1-7, no 0 values.

The query for the layer is just this below. The only way I have been able to fix this issue is to replace the explicit selection of ROUTE_TYPE in the SQL query with just 'SELECT *', and then the values appear to display correctly in the Attributes Table.

SELECT ROUTE_TYPE from main.Roads

I am just wondering if anyone else has experienced this? I am not sure if there is a strange bug here or I am doing something wrong.

ASw93 · Answer

No need to apologise for questions, I appreciate you looking into it.

I just used the Layer Properties > Source > Set Data Source option to select the feature class from my SQLite database, same as I would if connecting to a file geodatabase. And then the Query option is used to choose which fields are selected from the SQLite table.

Edit: Looks like I actually did this through Python, rather than the Arc GUI, using 'lyr.updateConnectionProperties()'.

KimberlyGarbade · Answer

How did you make the connection to your SQLite database in the "Edit Query Layer" dialog?

I don't use SQLite database very often and I can't seem to connect via this dialog to test it.  I know I'm asking more questions than I'm answering, but trying to wrap my head around this.

ASw93 · Answer

You are correct with the problem summary, it's a bizarre issue!

The "SELECT ROUTE_TYPE FROM main.Roads" query is in the Layer Properties > Source > Query box.

Thankfully changing to 'SELECT *' corrects the issue and the features will draw, but my Map contains a lot of layers that are seeing this issue so it is a lot of fixing to do.