Alternative solution for Sub Queries with Standardized Queries

[SCS_ANST]

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

[TanuHoque]

@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-... 

[SCS_ANST · Accepted solution]

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.