topic Re: Invalid Where clause - Left function - Expression Builder - Arcade - Attribute Rule in Developers Questions

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

hw_mwf — Wed, 07 Aug 2024 14:38:24 GMT

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?

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

KenBuja — Wed, 07 Aug 2024 14:59:49 GMT

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);

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

hw_mwf — Wed, 07 Aug 2024 15:22:04 GMT

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

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

KenBuja — Wed, 07 Aug 2024 15:33:18 GMT

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

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

hw_mwf — Wed, 07 Aug 2024 15:43:43 GMT

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

return cur_unitid_max.UNITID;

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

KenBuja — Wed, 07 Aug 2024 15:57:37 GMT

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;

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

hw_mwf — Wed, 07 Aug 2024 16:02:58 GMT

Still the same error.

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

KenBuja — Wed, 07 Aug 2024 16:09:54 GMT

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);

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

hw_mwf — Wed, 07 Aug 2024 17:08:15 GMT

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

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

KenBuja — Wed, 07 Aug 2024 18:11:54 GMT

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);

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

hw_mwf — Wed, 07 Aug 2024 18:31:52 GMT

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')'

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

KenBuja — Wed, 07 Aug 2024 18:42:12 GMT

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");

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

hw_mwf — Wed, 07 Aug 2024 18:54:27 GMT

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"));

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

KenBuja — Wed, 07 Aug 2024 19:06:52 GMT

What is the value of $feature.WINDEX?

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

hw_mwf — Wed, 07 Aug 2024 19:16:07 GMT

HC58W

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

KenBuja — Wed, 07 Aug 2024 19:28:33 GMT

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

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

hw_mwf — Fri, 09 Aug 2024 18:45:18 GMT

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!

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

hw_mwf — Thu, 22 Aug 2024 17:13:15 GMT

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