topic Definition Query - CASE Statement in ArcGIS Pro Questions

Definition Query - CASE Statement

MatthewGeorge — Tue, 15 Nov 2022 23:53:10 GMT

Does anyone know if a CASE statement can be used in a definition query in ArcGIS Pro?

For example:

SomeField = CASE WHEN MONTH(GETDATE()) >= 8 THEN CONCAT(YEAR(GETDATE()), "-", YEAR(GETDATE())+1) ELSE CONCAT(YEAR(GETDATE())-1, "-", YEAR(GETDATE())) END

This statement works fine when run from SSMS on the versioned view but not from a definition query.

Thanks in advance.

Re: Definition Query - CASE Statement

VinceE — Fri, 18 Nov 2022 19:52:26 GMT

My understanding is that Pro has a pretty limited flavor/implementation of SQL, and the CASE statement is not available. The only things I can point to supporting this are a lack of mentions of "CASE" in the Esri documentation, as well as Manifold complaining about how Esri doesn't support much in the way of SQL, but Manifold DOES through their third-party extension. Take their marketing pitch with a grain of salt perhaps, I've never used their software.

Quote from the Manifold docs: https://sql4arc.com/info/sql_compared.shtml

"SQL for ArcGIS Pro provides an extensive choice of data types, including a variety of geometry and vector (tuple) types, and operators, including tuple operators, CASE, CASE WHEN, CAST, CASTV, and much more. There are very long listings in the various SQL topics in the SQL for ArcGIS Pro user manual."

Re: Definition Query - CASE Statement

MatthewGeorge — Fri, 18 Nov 2022 22:18:18 GMT

Thanks Vince, I guessed as much. Looks like I'll have to add a new field to achieve the filtering I'm chasing. I'm going to add this as an idea and see if it gets any traction.