topic Alternative solution for Sub Queries with Standardized Queries in ArcGIS Enterprise Questions

Alternative solution for Sub Queries with Standardized Queries

SCS_ANST — Tue, 19 Sep 2023 13:22:07 GMT

Hi all,

today, we've implemented a solution with subqueries as where-parameters in the requests, as the standardized queries option was disabled so far.

Now, for security reasons, we have to activate this standardized queries option on our ArcGIS Enterprise platform.

Our Case described in a high level view:

Table "CELLS":

CELL_ID, CELL_NAME
1, ABCD1A
2, ABCD2A
3, ABCD3A

Table "ALARMS":

ALARM_ID, CELL_ID, ALARM_ON, ALARM_OFF
1000, 1 , 1.1.2020 08:00, 1.1.2020 09:00
1001, 1 , 2.1.2020 12:00, NULL
1002, 2, 1.1.2020 08:00, 1.1.2020 09:00

Our map service has a query layer with source: "SELECT * FROM CELLS"

With the where clause we filtered so far in the requests with: "1=1 AND CELL_ID IN (SELECT CELL_ID FROM ALARMS WHERE ALARM_OFF IS NULL)" to receive all cells with an open alarm.

(We have further scenarios for filtering with attributes and those filters are dynamically concatenated regarding users settings).

Now with standardized queries option, this subqueries will no longer be possible.

What's the best practice for cases like this?

Thank you for every hint.

Regards
Stefan

Re: Alternative solution for Sub Queries with Standardized Queries

TanuHoque — Wed, 22 Nov 2023 00:46:49 GMT

@SCS_ANST

pls check and see if this approach helps

https://pro.arcgis.com/en/pro-app/latest/help/mapping/layer-properties/define-parameters-in-a-query-layer.htm

Re: Alternative solution for Sub Queries with Standardized Queries

SCS_ANST — Wed, 29 Nov 2023 09:58:37 GMT

Hi @TanuHoque

that's what exactly we've implemented now. It was more difficult to use them with the javascript api. Here the solution was parameterValues (https://developers.arcgis.com/rest/services-reference/enterprise/query-map-service-layer-.htm).

Problem solved so far for us. Thank you for your support.