SQL query doesn't filter nulls for label expressions?

JeffThomasILM

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:

<SPAN class="keyword token">def</SPAN> FindLabel <SPAN class="punctuation token">(</SPAN> <SPAN class="punctuation token">[</SPAN>ValMap<SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
  <SPAN class="keyword token">return</SPAN> <SPAN class="punctuation token">[</SPAN>ValMap<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"_"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN>‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

You'll get this error:

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

<SPAN class="keyword token">def</SPAN> FindLabel <SPAN class="punctuation token">(</SPAN> <SPAN class="punctuation token">[</SPAN>ValMap<SPAN class="punctuation token">]</SPAN> <SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
  <SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">[</SPAN>ValMap<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">!=</SPAN> None<SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">return</SPAN> <SPAN class="punctuation token">[</SPAN>ValMap<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">.</SPAN>split<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"_"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN>‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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:

<SPAN class="keyword token">SELECT</SPAN> <SPAN class="operator token">*</SPAN> <SPAN class="keyword token">FROM</SPAN> FeatureClass <SPAN class="keyword token">WHERE</SPAN> ValMap <SPAN class="operator token">IS</SPAN> <SPAN class="operator token">NOT</SPAN> <SPAN class="token boolean">NULL</SPAN>‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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.

XanderBakker

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:

<SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="operator token">!</SPAN><SPAN class="token function">IsEmpty</SPAN><SPAN class="punctuation token">(</SPAN>$feature<SPAN class="punctuation token">.</SPAN>ValMap<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
    <SPAN class="keyword token">var</SPAN> lst <SPAN class="operator token">=</SPAN> <SPAN class="token function">Split</SPAN><SPAN class="punctuation token">(</SPAN>$feature<SPAN class="punctuation token">.</SPAN>ValMap<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"_"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">;</SPAN>
    <SPAN class="keyword token">if</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="token function">Count</SPAN><SPAN class="punctuation token">(</SPAN>lst<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">==</SPAN> <SPAN class="number token">2</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="punctuation token">{</SPAN>
        <SPAN class="keyword token">return</SPAN> lst<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>
    <SPAN class="punctuation token">}</SPAN> <SPAN class="keyword token">else</SPAN> <SPAN class="punctuation token">{</SPAN>
        <SPAN class="comment token">// only 1 element, return first element?</SPAN>
        <SPAN class="keyword token">return</SPAN> lst<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">;</SPAN>
    <SPAN class="punctuation token">}</SPAN>
<SPAN class="punctuation token">}</SPAN> <SPAN class="keyword token">else</SPAN> <SPAN class="punctuation token">{</SPAN>
    <SPAN class="comment token">// input is empty, return empty string</SPAN>
    <SPAN class="keyword token">return</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">;</SPAN>
<SPAN class="punctuation token">}</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>