topic How to create a list out of an array for a SQL IN Operator in ArcGIS Pro Questions

How to create a list out of an array for a SQL IN Operator

TL2 — Sat, 11 Dec 2021 13:12:57 GMT

I have a arcade expression that loops through a set of records and produces a comma separated list of parentrowids.

    for (var p in parentrowids) {
        array +=    p.parentrowid + ", "   ;      
    }
} 
var prid_array =  array; 
 }‍‍‍‍‍‍

I have a where clause a few lines down that uses the IN operator to filter on another table. I get this error not matter how I try and produce the "array".

When I write the value to a table using this string:

var prid_array =  "('"+ array + "')"; ‍

I get this:

However the table is populated as

('{67D92A25-79C7-4225-9AD1-C140662DEFCF}', '{F269CECB-70D3-4288-8D11-102025D3D701}', '{99AB8E1B-2759-43CA-88DE-7A5A2FDECDC6}', '')

Is there a way to output a string to be used in a IN operator?

Xander Bakker‌

Re: How to create a list out of an array for a SQL IN Operator

XanderBakker — Sat, 11 Dec 2021 13:13:00 GMT

Hi T L ,

No sure if I would use a SQL expression with IN on GlobalIDs. It may requiere some additional formatting. If I apply some of the logic that I have used before it could be something like this:

var prid_array = "uniquerowid IN (";
var lst = [];
for (var p in parentrowids) {
    lst[Count(lst)] = "'{" + Upper(p.parentrowid) + "}'";
} 
prid_array += Concatenate(lst, ",") + ")";‍‍‍‍‍‍

However, I think since you are working on GlobalIDs there will probably be a relationship, so maybe you could get the related features for each of the GlobalIDs from the list and process them as you need.

Re: How to create a list out of an array for a SQL IN Operator

XanderBakker — Wed, 12 Aug 2020 20:03:34 GMT

Hi T L ,

Just an additional comment. When I need to verify if the syntaxis of a SQL is correct, I normally use Pro and construct the definition query and switch to SQL to see how it is formatted: