SQL Statement within Attribute Rule Calculation Error

OliverSandoval_p — Mon, 25 Jul 2022 22:03:02 GMT

Hello Everyone,

I'm having an issue with an sql statement within a calculation attribute rule. So basically I want to filter related data based of guid and a 'last clean' date field that's not null, AND is also within this year. The first two statements are fine on thier own, but the calculation fails when I query for year.

Here is a part of my code for context

var CB_gid = $feature.GlobalID; var this_year= Year(now()); var filter_statement = 'guid = @CB_gid AND last_cleaned IS NOT Null AND YEAR(last_cleaned) = @this_year)' //an example of this which works fine in pro can be seen below guid = '{F5C7CC47-6B71-439A-A144-834C9696C262}' AND last_cleaned IS NOT Null AND YEAR(last_cleaned) = '2022'

This a part of the error that occurs when trying to run the calculation.

Just for some more context, this filter is just one of possible three filters that occurs based off three if statements. The other two work fine, just not this one. And there is also a workaround I can do with more arcade variables for beginning of the year date and end of year date, but id rather have it in sql statement.

Any help would be greatly appreciated!

Thank you!

Re: SQL Statement within Attribute Rule Calculation Error

JohannesLindner — Tue, 26 Jul 2022 06:03:04 GMT

According to your working query, 2022 should be provided as string, but it's an integer.

Try these approaches:

// convert this_year to string var CB_gid = $feature.GlobalID; var this_year= Text(Year(Now())); var filter_statement = 'guid = @CB_gid AND last_cleaned IS NOT Null AND YEAR(last_cleaned) = @this_year)'

// take care of the types yourself var filter_statement = `guid = '${$feature.GlobalID}' AND last_cleaned IS NOT Null AND YEAR(last_cleaned) = '${Year(Now())}')`

topic SQL Statement within Attribute Rule Calculation Error in Attribute Rules Questions

SQL Statement within Attribute Rule Calculation Error

Re: SQL Statement within Attribute Rule Calculation Error