Invalid Where clause - Left function - Expression Builder - Arcade - Attribute Rule

[hw_mwf]

Trying to create an attribute rule (immediate calculation rule) on a feature class within an Enterprise Geodatabase in Oracle (.sde) within ArcGIS Pro 2.9.5. I want to filter to improve performance. Specifically, selecting only records when the first five characters of the UNITID text field equal the WINDEX text field. I am starting with the following two lines of Arcade code in the Expression Builder and when I click the green Verify check mark, its says 'Expression is valid'.

var cur_unitid = Filter(FeatureSetByName($datastore, "WSL", ['UNITID'], false), 'Left(UNITID, 5) == $feature.WINDEX');
var cur_unitid_desc = OrderBy(cur_unitid, 'UNITID DESC');

However, when I add the following third line of code and click the green Verify check mark, it says 'Invalid expression. Error on line 3. Invalid where clause (Left(UNITID, 5) == $feature.WINDEX)'.

var cur_unitid_max = first(cur_unitid_desc);

Can the Left function be used on a feature class field name and/or is there an error in how I am using it?

[KenBuja]

Your sql expression is invalid, since it doesn't understand Arcade syntax (including variables such as $feature). You can only use SQL92 syntax. An expression like this should work

var value = $feature.WINDEX;
var cur_unitid = Filter(FeatureSetByName($datastore, "WSL", ['UNITID'], false), "Substring(UNITID, 1, 5) = @value");
var cur_unitid_desc = OrderBy(cur_unitid, 'UNITID DESC');
var cur_unitid_max = first(cur_unitid_desc);

[hw_mwf]

Thanks for the reply. The first 3 lines of your code yield 'Expression is valid', however the 4th line yields the error 'Invalid expression. Error on line 4. General evaluation error'.

[KenBuja]

Have you tried completing the rule, adding in what you want to return?

[hw_mwf]

I added the below as the 5th line, and the error remains (Error on line 4. General evaluation error).

return cur_unitid_max.UNITID;

[KenBuja]

What happens with this code? I know it's not giving the maximum record, but it's just testing out the filtered result.

var value = $feature.WINDEX;
var cur_unitid = Filter(FeatureSetByName($datastore, "WSL", ['UNITID'], false), "Substring(UNITID, 1, 5) = @value");
//var cur_unitid_desc = OrderBy(cur_unitid, 'UNITID DESC');
var cur_unitid_max = First(cur_unitid);
return cur_unitid_max.UNITID;

[hw_mwf]

Still the same error.

[KenBuja]

Let's check whether you're getting anything back from the Filter

var value = $feature.WINDEX;
var cur_unitid = Filter(FeatureSetByName($datastore, "WSL", ['UNITID'], false), "Substring(UNITID, 1, 5) = @value");
return Count(cur_unitid);

[hw_mwf]

This is now the error 'Invalid expression. Error on line 3. Failed to query statistics.

[KenBuja]

The testing I've done in the Playground using the example public portal item has worked correctly.

Are you sure you have the correct information for the FeatureSetByName function? What happens with this code?

var cur_unitid = FeatureSetByName($datastore, "WSL", ['UNITID'], false);
return Count(cur_unitid);