nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Using SQL to "Select by Attributes" comparing fields...

AdrianWelsh

I'm trying to write a simple SQL query that just isn't working. I have two fields in my file geodatabase layer (ArcGIS Pro 2.3) and I want to select the records that match my simple SQL query of:

written out to LEFT(Layer,3) = OWNERSHIP_NUMBER

But I get an error saying that "The SQL expression has invalid syntax".

I must be doing it wrong somewhere but I don't know where. These are both FIELDS and not RECORDS, so that likely makes a difference in how my syntax is supposed to be written.

Looking up this article: SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop

it says "

String functions can be used to format strings. For instance, the LEFT function would return a certain number of characters starting on the left of the string. In this example, the query would return all states starting with the letter A:
LEFT(STATE_NAME,1) = 'A'

So, it makes me think my syntax is mostly right since both of my fields are strings. Any advice on making my SQL query syntax work?

Encuentra más publicaciones etiquetadas con

Accepted answers

RandyBurton

I think since you are using a file geodatabase you might need to use something like (you'll want to make sure both sides are a string of 3 characters):

SUBSTRING("OWNERSHIP_NUMBER" FROM 1 FOR 3) = SUBSTRING("Layer" FROM 1 FOR 3)<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

(This from an old thread: Definition query SQL won't accept Left() Function)

And according to SQL reference for query expressions used in ArcGIS

All SQL used by the file geodatabase is based on the SQL-92 standard.

All comments

AdrianWelsh

Geeze, you're right! This is a great segment from that article:

The result of each of the following comparisons is therefore unknown:0
NULL = 1 
NULL <> 1 
NULL > 1 
NULL = NULL
Nothing equals null. Not even null equals null because each null could be different.

Haha, oh null. What a conundrum

RandyBurton

And the page referenced also says:

A Lot Has Changed Since SQL-92

And so it has.

bixb0012

The presence of NULL introduces three-value logic into SQL comparison operators. What you are seeing regarding your comparisons with NULL is expected. It might be worth checking out Modern SQL: Three-Valued Logic (3VL) — Purpose, Benefits and Special Cases

AdrianWelsh

Randy,

Nice digging on that old thread. Your suggestion mostly worked! I don't know why it didn't all-the-way work but that is on my end.

(my example is, after the query of

SUBSTRING("Layer" FROM 1 FOR 3) <> SUBSTRING("OWNERSHIP_NUMBER" FROM 1 FOR 3)<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

wherever I have null values, these were not part of my selection - so, Layer would = 144B and OWNERSHIP_NUMBER would = <null>, and these are clearly different, this record was not selected. This is true when I flip-flop the query as well.).

Either way, this gets me past my initial question of why LEFT didn't work. Thanks again!

(maybe Esri should change that documentation........)

AdrianWelsh

Thanks for sharing that Randy. I'll have to check it out tomorrow to see if it will work. I'll report back here!

bixb0012

I just tried in Pro 2.3, 10.7 Prerelease, and 10.3.1; and all of them generated the same invalid syntax error. Maybe 9.x?

RandyBurton

At one time, I would swear that I used "LEFT" with a file geodatabase. But it wasn't working today ... which I attributed to just being a Monday!

bixb0012

Randy, I believe your comment is correct, at least in terms of making it work. Adrian's confusion originates from 2 facts: 1) "LEFT" is commonly implemented in DBMSs, and 2) Esri's own documentation clearly states "LEFT" should work.

RandyBurton

I think since you are using a file geodatabase you might need to use something like (you'll want to make sure both sides are a string of 3 characters):

SUBSTRING("OWNERSHIP_NUMBER" FROM 1 FOR 3) = SUBSTRING("Layer" FROM 1 FOR 3)<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

(This from an old thread: Definition query SQL won't accept Left() Function)

And according to SQL reference for query expressions used in ArcGIS

All SQL used by the file geodatabase is based on the SQL-92 standard.

AdrianWelsh

Hmmm, that gave me the same error.

Details: both fields are text fields and contain numbers.

ownership_number are normally 3 digit numbers that sometimes contain a letter. Examples are 100, 101, 102, 102B, etc.

Layer has the same three digit numbers with additional info usually in the case of colons. So, examples: 100:E 100:PUE, 101:ST, etc.

I just want this SQL query to work so I can modify it to narrow down my search, etc. (finding where these numbers do not match). Thanks for your help!

(Bonus, does it matter that one text field is 255 characters long and the other is 8000 characters?)

RandyBurton

My initial reaction is that it should be written as field = some value, therefore:

OWNERSHIP_NUMBER = LEFT(Layer,3)<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

This is assuming that ownership number is text, as Left is a text function. It might be helpful if you could share an example of ownership_number and layer, if you are still having problems. Hope this helps.

Únete a la Conversación

Los miembros registrados pueden publicar, seguir actualizaciones y más. ¿Nuevo aquí? Regístrate gratis.

Enlaces Rápidos

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.