Comments in SQL expressions — Single-line comment at and of statement causes error

Bud · ‎02-06-2023

ArcGIS Pro 3.0.3; mobile geodatabase:

I want to put SQL comments in definition query SQL expressions. From my testing, these kinds of comments seem to work as expected:

Works: Multi-line SQL comments at the beginning or end of the SQL expression:

/*multi-line sql comment*/
OBJECTID IN (3, 5, 11)

OBJECTID IN (3, 5, 11)
/*multi-line sql comment*/

Works: Single-line SQL comments at the beginning of the SQL expression:

--single-line sql comment
OBJECTID IN (3, 5, 11)

Doesn't work: Single-line SQL comments at the end of the SQL expression:

OBJECTID IN (3, 5, 11)
--single-line sql comment

Error: The SQL expression has invalid syntax.

Question:

Why don't single-line comments work at the end of SQL expressions in ArcGIS Pro? Is that a bug?

I don't have that issue when creating full-blown SELECT queries in database views, query layers, or SQL Developer queries. I only seem to have that problem with ArcGIS Pro SQL expressions.

Note: There is a bug in ArcGIS Pro 3.0.3 that can be a nuisance: Modifying existing SQL expression disables definition query. So when testing things like the comments/SQL above, we need to re-enable definition queries after modifying the SQL.

Bud

Esri Canada Case 03268522 - Comments in SQL expressions - Single-line comment at and of statement causes error

ENH-000156157 - ArcGIS Pro - Add a line break automatically to the end of any definition query SQL expression applied to a mobile geodatabase feature class to avoid the error "The SQL expression has invalid syntax." when a SQL single-line comment that is not terminated by a line break is inserted at the end of the expression.

ENH-000156184 - ArcGIS Pro - Change the error message displayed when a SQL single-line comment that is not terminated with a line break and that doesn't include a space is inserted at the end of a definition query SQL expression applied to a mobile geodatabase feature class from "Fields do not match." to "The SQL expression has invalid syntax.".

ENH-000156189 - ArcGIS Pro - Add documentation that outlines how to add single-line and multi-line comments to a definition query SQL expression applied to a mobile geodatabase feature class.