Hello, I am using ArcGIS Pro 3.4 and have a definition query on a layer set to display a records as long as the value is not "hidden". However, if a record is then the record does not display at all. I would expect any null records to show up on the map because they are not equal to hidden. Am I missing something? SQL Check <> 'Hidden' Thanks!

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; } } }

Definition query behavior with null records

LyonMNGIS

Hello,

I am using ArcGIS Pro 3.4 and have a definition query on a layer set to display a records as long as the value is not "hidden". However, if a record is <null> then the record does not display at all.

I would expect any null records to show up on the map because they are not equal to hidden. Am I missing something?

SQL

Check <> 'Hidden'

Thanks!

Trouver plus de publications taguées avec

Desktop

Accepted answers

bixb0012

Regarding

@LyonMNGIS wrote:
...
I would expect any null records to show up on the map because they are not equal to hidden. Am I missing something?
...

The existence or possibility of NULL means SQL operators implement three-valued or ternary logic. Instead of the true/false outcomes of boolean logic, there are true/false/unknown outcomes. If you write:

check <> 'Hidden'

The records that are NULL return unknown, not true or false. Since the result is unknown, the database will not return them because it is possible they could be 'Hidden', and it only returns records that are guaranteed not to be 'Hidden'.

There are people who strongly oppose allowing or using NULL in any data model, including CJ Date who is credited with creating the relational model that SQL databases partially implement.

I will note that prior to ArcGIS Pro 3.3, Esri's implementation of three-valued logic with certain operators in the file geodatabase was inconsistent with SQL standards and the rest of the industry. Esri fixed the issue to be consistent with everyone else at Pro 3.3, but it also means certain queries can produce different results between Pro 3.2 and earlier and Pro 3.3 and later.

All comments

TaylorGreenup

I just came across this same issue with an ArcPro 3.3.6 definition query. Null values aren't that uncommon in datasets and it seems odd that they get pulled into a selection for something being specificized. Select By Attributes also works on SQL but doesn't behave in this manner.

In order to get the result I wanted, I had to add on an OR statement to my definition query. And I just realized I didn't read far enough down to see that Robert has the same solution. 🙂

LyonMNGIS

Robert,

Thank you I did add or hidden is null and everything works as expected.

LyonMNGIS

Joshua,

Thank you for your help. That would explain why the behavior seemed to have changed.

bixb0012

Regarding

@LyonMNGIS wrote:
...
I would expect any null records to show up on the map because they are not equal to hidden. Am I missing something?
...

check <> 'Hidden'

There are people who strongly oppose allowing or using NULL in any data model, including CJ Date who is credited with creating the relational model that SQL databases partially implement.

Robert LeClair

You're almost there! You need to add one more SQL statement to get the features that don't have hidden in them or there is a NULL value in the field. Here's the SQL statement that worked for me:

Check <> 'Hidden' Or Hidden IS NULL

This will show all values NULL or other attribute values but NOT show Hidden.

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Se connecter
S'inscrire

Liens rapides

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