Support Distinct : false ? how to enable in map service ?

[MayurDodiya]

Hi,

I published map service on ArcGIS Server 10.3.1, I see in Advanced Query Capabilities section "Supports Distinct : false" in rest directory, When I try to query using Query with query.returnDistinctValues = true; parameter, It doesn't return unique values from the layer.

I don't see option in capabilities while you publish map service on ArcGIS Server.

How to enable "Supports Distinct : true"  ?

Am I missing anything ?

Thanks,

Mayur

[ThomasJones1]

Hello Mayur Dodiya‌,

Does the map service support 'supportsAdvancedQueries: true'? What is the data source for the map service?

Thanks,

Thomas.

[MayurDodiya]

Hi Thomas,

Support Advanced Queries : false in rest directory. The Data Source is ArcGIS Enterprise Geodatabase Oracle 11g.

Layer "Parcel" is join with "View " based on "PIN" field and published on ArcGIS Server.

Thanks,

Mayur

[TanuHoque]

Mayur Dodiya‌

How to enable "Supports Distinct : true"  ?

You can't do that. A map or feature service figures that out based on whether the underlying datasource supports this.

I guess it is the join that is throwing it off. Are the Parcel layer and the view coming from the same workspace - with that I mean did you use the same connection (.sde) file to add both of them in the map?

If they are coming from the same worksapce, then I think it could be a bug and please reach out to Esri Support to open a ticket for this issue.

[MayurDodiya]

Hi Tanu,

They are coming from different worskspace, Parcel layer from Geodatabase enterprise Oracle 11g and View from SQL Server (Non-Spatial) database.

Thanks,

Mayur

[TanuHoque · Accepted solution]

Mayur,

Yes, that is expected. When feature-classes/tables participating in a join come from different workspaces, supportsAdvancedQueries is set to false and a query operation can't return distinct values.

It is done this way for best performance - map service delegates works to the underlying database for queries such as distinct, out statistics etc. When tables are coming from two different databases, this approach does not work.

Please see the REST API doc: https://developers.arcgis.com/rest/services-reference/layer-table.htm 

supportsAdvancedQueries would return false in the following scenarios:

• The layer / table resides in a workspace other than an enterprise database or File Geodatabase.
• The layer / table is joined with another layer / table from a different workspace.

Only resolution that I can think for your problem is to, if possible, bring both Parcel and the view in to one database.

hope this helps.

Thanks.

Tanu