Data not displaying correctly in Attribute Table (SQLite)

[ASw93]

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.

[KimGarbade]

In the first image example above (all zeros) when you say you "open the Attribute Table for this layer", what method are you using to open it?  

[ASw93]

I just right-clicked the layer in the Contents pane and pressed 'Attribute Table'. This field is also used in the symbology, so the features do not draw as the symbology isn't expecting zeroes. If I change the layer query to just 'SELECT *', ROUTE_TYPE values display correctly. I've never seen an issue like this before.

[KimGarbade]

Sorry about all the questions but just to  be clear: you see all zero's when you open the table from the Contents pane and you see the Route_Type field populated correctly when you open the same table from the Catalog pane?  Hmmm...that is odd in my experience. 

Where (in what context or dialog box) are you using the "SELECT ROUTE_TYPE FROM main.Roads" query (or the "SELECT * FROM main.Roads" query)? Again sorry about all the questions.

[ASw93]

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.

[KimGarbade]

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]

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()'.