ArcGIS Server – Why ArcGIS Server Does Not Return Data When Querying Joined Fields

arcgis-10.1arcgis-servergeocortexquery

I've been trying to help troubleshoot an issue that is very strange, and I think we've boiled it down to an issue with ArcGIS Server.

We have a map service that shows up in the services directory as:

Layers:
    Some-LayerA (0)
    ...
    Some-LayerE (4)

Tables:
    databaseName.SchemaName.TableName (5)

And the layer we are querying against looks roughly like this:

Fields:
    databaseName.SchemaName.LayerName.ObjectID (type: esriFieldTypeOID, alias: OBJECTID)
    databaseName.SchemaName.LayerName.SomeField (type: esriFieldTypeString, alias: SomeField, length: 3)
    databaseName.SchemaName.TableName.ProjectKey (type: esriFieldTypeString, alias: ProjectKey, length: 18)
    databaseName.SchemaName.TableName.Spent To Date ( type: esriFieldTypeDouble , alias: Spent To Date ) 
    ... More joined fields (No duplicate names or aliases, all strings, dates, doubles)

When issuing a query with only a where clause against the joined table, I can do something like this and have it bring results up:

Where: databaseName.SchemaName.TableName.ProjectKey = 'ValidProjectKey'

--> Returns 2 results
-----> databaseName.SchemaName.TableName.ProjectKey: ValidProjectKey

-----> databaseName.SchemaName.TableName.ProjectKey: ValidProjectKey

However, if I add some extra parameters, then I get no results at all:

Where: databaseName.SchemaName.TableName.ProjectKey = 'ValidProjectKey'
Out Fields: *

---> Returns 0 results

Apparently, if specifying the out fields manually, it will give results:

Where: databaseName.SchemaName.TableName.ProjectKey = 'ValidProjectKey'
Out Fields: databaseName.SchemaName.TableName.ProjectKey

---> Returns 2 results
-----> databaseName.SchemaName.TableName.ProjectKey: ValidProjectKey

-----> databaseName.SchemaName.TableName.ProjectKey: ValidProjectKey

But, if I change that second query to go against one of the layer's native (non-joined) fields instead, then it seems to return an appropriate number of results, but with all of the join fields set to null:

Where: databaseName.SchemaName.LayerName.SomeField = 'SomeValue'
Out Fields: *

---> Returns 514 results
------> databaseName.SchemaName.LayerName.ObjectID: 553
------> databaseName.SchemaName.LayerName.SomeField: SomeValidValue
------> databaseName.SchemaName.TableName.ProjectKey: null
------> databaseName.SchemaName.TableName.ProjectManager: null

------> databaseName.SchemaName.LayerName.ObjectID: 1938
------> databaseName.SchemaName.LayerName.SomeField: SomeOtherValidValue
------> databaseName.SchemaName.TableName.ProjectKey: null
------> databaseName.SchemaName.TableName.ProjectManager: null

Any clues why going against the joined table would lead to such different behavior? I don't have much control over the actual query, because it is being issued through geocortex.

Best Answer

Thanks to the suggestion from mwalker about trying to query each field manually (rather than using *), I think we finally found the issue.

It ends up that I should have looked more closely at the list of fields in the joined table. Out of the 10 fields in the joined table that had compound field names, 5 of them were done in PascalCase, and 5 used spaces. (e.g. "Classified As" instead of "ClassifiedAs" or "Classified_As")

After republishing the table with more consistent naming, all queries seem to be behaving as expected!

So, end result is the behavior seems to be caused by improper field naming.

Related Question