topic Re: SQL query doesn't filter nulls for label expressions? in ArcGIS Pro Questions

SQL query doesn't filter nulls for label expressions?

JeffThomasILM — Sun, 12 Dec 2021 05:41:43 GMT

I'm trying to split a string and label only a particular part of it. (I'm using Python because Arcade doesn't seem to have sufficient capability to specify which part of the string I want to label, but that's a separate gripe.)

This expression cannot be verified if any nulls are present in the field:

def FindLabel ( [ValMap] ):
  return [ValMap].split("_")[1]‍‍‍‍‍‍‍‍

You'll get this error:

But you can get around that by putting an if statement in the expression:

def FindLabel ( [ValMap] ):
  if [ValMap] != None:
    return [ValMap].split("_")[1]‍‍‍‍‍‍

But then I thought, "Wait, I shouldn't have to filter nulls in the expression; that's what the SQL query on the label class is for!" I had already applied this filter to the label class:

SELECT * FROM FeatureClass WHERE ValMap IS NOT NULL‍‍

For testing, I applied the same SQL query to the definition query of the layer. Either way, it doesn't prevent the error on the expression. I thought maybe this is a bug in Pro but the same thing happens in ArcMap, which makes it less likely to be a bug(?). So now I just want to understand the mechanics of SQL queries on label classes and layer definitions, and their apparent (non-)effect on label expressions.

Re: SQL query doesn't filter nulls for label expressions?

XanderBakker — Sun, 12 Dec 2021 05:41:45 GMT

Hi Jeff Thomas ,

That is a good question. When a situation is filtered out why does it still produces an error? Maybe the answer lies in the order in which something is executed or validated. Perhaps the filtering occurs after applying the expression. Whatever the reason might be, it is important to account for different situations in your data now and in the future. When a text does not have space, and you try to access the second element after doing the split, this will also result in an error. It is good practice to account for this types of errors.

Regarding using Arcade, you can do the same. See example below:

if (!IsEmpty($feature.ValMap)) {
    var lst = Split($feature.ValMap, "_", 2);
    if (Count(lst) == 2) {
        return lst[1];
    } else {
        // only 1 element, return first element?
        return lst[0];
    }
} else {
    // input is empty, return empty string
    return "";
}‍‍‍‍‍‍‍‍‍‍‍‍

Re: SQL query doesn't filter nulls for label expressions?

JeffThomasILM — Thu, 17 Oct 2019 17:15:21 GMT

Thanks, Xander. I figured it might be order of operations, but was wondering why Esri would design label expressions to ignore or precede layer and label class queries. In what situation would that be desirable? Or what technical issue requires label expressions to go first?

And thank you for the Arcade example!