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?

[hw_mwf]

So that code does verify to 'Expression is valid'. But when I go back to adding in a filter it still errors.

I know the first record's UNITID is HC58W017. If I try this code:

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

It says 'Failed to query statistics'. If I try this code:

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

It says 'Invalid where clause ('HC58W0187')'

[KenBuja]

It won't filter correctly since the variable value has to be five characters long. Try this instead

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

[hw_mwf]

So just those two lines; 'Expression is valid'. But when I add this 3rd line, I get 'Error on 3rd line. Failed to query statistics'

return Count(cur_unitid);

Or if I don't add the 3rd line and use the first function on the 2nd line, the error is 'General evaluation error':

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

[KenBuja]

What is the value of $feature.WINDEX?

[hw_mwf]

HC58W

[KenBuja]

Honestly, I'm stumped on this. I don't have access to a dataset on Oracle, so I can't do further testing to see how the syntax may be different..

[hw_mwf · Accepted solution]

Agreed. The code works when the feature class is in a .gdb but not in a .sde. Specifically, the First function fails with a 'General evaluation error'. You did solve my initial problem (use a Substring function instead of Left), so I'll 'Accept as Solution' and repost this additional issue if I cannot figure it out. Thanks!

[hw_mwf]

Just to update the 'General evaluation error', I have upgraded to ArcPro 3.3 and the error is now gone.