topic Issue with SQL Expression valid/invalid in ModelBuilder Questions

Issue with SQL Expression valid/invalid

cartezj1 — Fri, 01 Oct 2021 15:42:59 GMT

I'm using ArcGIS Pro 2.8.

Using Modelbuilder, I am attempting to select by attribute. I validate the expression and it returns that it is valid. However, when I attempt to run the model it returns ERROR 000358: Invalid Expression. The input is from an ArcGIS Server connection. Not sure what I'm missing here. I've attached a screenshot.

Re: Issue with SQL Expression valid/invalid

curtvprice — Fri, 01 Oct 2021 15:47:48 GMT

The expression may be valid but not for that particular data source. Have you tried running the tool outside ModelBuilder?

Re: Issue with SQL Expression valid/invalid

cartezj1 — Fri, 01 Oct 2021 16:13:04 GMT

Outside of modelbuilder, I'm receiving "The SQL expression has invalid syntax" if I keep the same code that is in the screenshot. When building the expression I receive "There was an error with the expression." These errors are when using the ArcGIS server and the same feature class from the SDE. The SDE is PostgreSQL which I don't have access to.

Re: Issue with SQL Expression valid/invalid

curtvprice — Sat, 02 Oct 2021 17:54:25 GMT

This sounds like a problem with your expression, not with ModelBuilder. SQL can vary between different ArcGIS data sources and especially different SQL databases, the expressions that work with some databases are not supported with others. I think you need to look at some PostgreSQL documentation and check the syntax for this type of query.

The "date" keyword may not be needed here, see:
https://sqlserverguides.com/postgresql-date-between-two-dates/

which uses:

SELECT name,end_date as left_date FROM employee WHERE end_date BETWEEN '1998-01-07' AND '2016-08-01';