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

[Bud]

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.

[gpgisandsky]

Hi Bud,

you have to complete the sql string with ";" then write your comment like these examples:

It's works on Pro 3.1.4. thank you

[JoshuaBixby]

Given that a semicolon has been the <SQL terminator> since the first SQL standard, and syntax rules have called for using a <SQL terminator> since the first SQL standard; this is definitely the correct answer.  In fact, I think addressing some of the logged defects and enhancements would make the situation worse rather than better.

[Bud]

Regarding a single-line comment at the end of the SQL:

I tried using a semicolon in ArcGIS Pro 3.2.1 and a mobile geodatabase. But I couldn't get it working.

What am I doing wrong?

Video:

The test in the video uses Select By Attributes. But, I see the same issue when doing a similar test in a definition query.

I also have the issue when using an Oracle 18c table in Pro 3.2.1 (unversioned).

Video:

Am I having a brainfart?

[gpgisandsky]

Hi Bud,

maybe:

a) definition query 

b) query filter

c) select by attribute

call the SQL in different way. The semicolon how <SQL terminator> and the comment and end of line (---pluto) or at a new line (/*paperino*/) works fine into a) and b) but in c) only <SQL terminator> is the cause of the error (I tested it on ArcGIS Pro 3.1.x with file geodatabase). The behavior you described could be related to how ArcGIS Pro processes SQL internally. May I ask you why you want put the comment into a temporarly query like select by attribute?

[Bud]

Definition queries are the main use case. I just happened to test in Select By Attributes because I thought it would behave the same way.

I couldn't get a semicolon to work in a definition query in ArcGIS Pro 3.2.1 / mobile geodatabase:

There was an error with the expression.

What do you mean by "query filter"?

[gpgisandsky]

Hi Bud, for mobile geodatabase don't put semicolon but try to put only this kind of comment at the end of line or at new line: /*pippo*/ likes :

it's works on my ArcGIS 3.1.x and mobile geodatabse. This confirm that ArcGIS work in different way on different format (file, personal gdb ...) too.

About "What do you mean by "query filter"?" I would say query filter in "display filter" 

have a good day.

[Bud]

for mobile geodatabase don't put semicolon but try to put only this kind of comment at the end of line or at new line: /*pippo*/

Yeah, I think I already mentioned that technique in the original post.