Arcade Expression using Distinct fails in SDE, not File GDB

[DavidColey]

Hello - I am putting together a relatively simple  Arcade script to eventually function as an attribute rule in order to create an identifier in a building footprint polygon by using a combination of an address number and unit number from address point layer.

The following works just fine in a file geodatabase, where $feature is my building footprint layer

var Address = FeatureSetByName($datastore, 'TRANS.AddressPoints', ['AddNumber', 'UnitNumber'],false); //, 'bldgname'
var intAddress = Intersects($feature, Address); //, 'bldgname'

var addNums = Distinct(intAddress, ['AddNumber']); // always 1 or gt 1
var untNums = Distinct(intAddress , ['UnitNumber']); // always 1 or gt 1, when 1 just use add number

var addCnt = Count(AddNums);
var untCnt = Count(untNums);
Console(addCnt);
Console(untCnt);

But when I run this against our current SQL2022 SDE instance to test a calculation in the Field Calculator (upgraded with the latest Pro 3.3.1 internals), it can't get past line 7, because it can't return a DISTINCT"

Invalid expression.
Error on line 7.Underlying DBMS error [[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The geometry data type cannot be selected as DISTINCT because it is not comparable.] [TRANS.AddressPoints][STATE_ID = 0]

I have to have a distinct array of values for both address number and unit number because they can repeat within a given footprint.  Any ideas?

@KenBuja ?

@rlyding 

[DavidColey · Accepted solution]

Ok so for whatever reason, the DISTINCT function is a constraint when trying to operate against SQL geometry.  Working with my collegue, this works against an SDE layer:

var Address = FeatureSetByName($datastore, 'TRANS.AddressPoints', ['AddNumber', 'UnitNumber'],false);
var intAddress = Intersects($feature, Address); //, 'bldgname'
var addNums = [];
for (var i in intAddress){
   //console(i);
    Push(addNums, i['AddNumber']);
}
addNums = Sort(Distinct(addNums));
console(addNums);
var addCnt = Count(addNums);
Console(addCnt);

By setting up an array first to store the Intersects return, and then passing the initial array into a sorted array, the DISTINCT function works or rather validates in the field calculator.

So I think I basically answered my own question.

@mstranovsky 

@rlyding 

@KenBuja