Using Calculate value for an sql statement

KitTompkins — Wed, 01 Feb 2023 20:00:41 GMT

I'm trying to use calculate value to create an sql expression for the Make Query Table Tool, and I've reached a point where I don't know why it's not working.

Here's my calculate value:

Expression = sqEX("%Name%") def sqEX(Table): fEx= "{}.Owner IS NOT NULL AND {}.Owner NOT LIKE '%FLO%'".format(*Table) return fEx

And this is the error message I'm getting back:

Executing (Calculate Value):
CalculateValue sqEX("ssSystemValve") "def sqEX(Table):
fEx= "{}.Owner IS NOT NULL AND {}.Owner NOT LIKE '%FLO%'".format(*Table)
return fEx"
"SQL Expression"
Start Time: Wednesday, February 1, 2023 11:41:31 AM

Value = s.Owner IS NOT NULL AND s.Owner NOT LIKE '%FLO%'

As you can see, the expression is showing the variable from the name field correctly, but then not passing it correctly to the string... I think it has something to do with the asterisk, but if I don't have that, it fails because there are two arguments in my format... thing. Do I need to write Table twice? Is there some other less stupid way to fix this problem?

Re: Using Calculate value for an sql statement

RhettZufelt — Wed, 01 Feb 2023 20:32:50 GMT

With python 3, f strings seem to work much more reliably with string substitutions like this.

Expression = sqEX("%Name%") def sqEX(Table): fEx= f"{Table}.Owner IS NOT NULL AND {Table}.Owner NOT LIKE '%FLO%'" return fEx

Since your code is assigning %Name% = 'ssSystemValve', I just substituted that for testing:

topic Using Calculate value for an sql statement in ArcGIS Pro Questions

Using Calculate value for an sql statement

Re: Using Calculate value for an sql statement