Feature request #6676

more sophisticated treating with NULL values in label expressions

Added by karpi lastname over 7 years ago. Updated over 7 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:-
Pull Request or Patch supplied:No Resolution:worksforme
Easy fix?:No Copied to github as #:15848

Description

Hello, there is piece from my postgis table:

karpidb3=> SELECT name, vyska FROM rozhledy_pt WHERE selcol = 1 ;
name | vyska
-----------+-------
Říp | NULL
Milešovka | 837

If I would like to label it by both fields. I can input this expression into qgis label expression dialog:
"name" || ' - ' || "vyska"

It is ok for many items, but where "vyska" IS NULL, label is not drawn.. (nor "name" field)
It right, as I understood, because it is the same like this sql query:

karpidb3=> SELECT name || ' - ' || vyska FROM rozhledy_pt WHERE selcol = 1 ;
?column?
-----------------
NULL
Milešovka - 837

But my requirement is more sophisticated, something like this query:

karpidb3=> SELECT concat(name, ' - ',vyska) FROM rozhledy_pt WHERE selcol = 1 ;
concat
-----------------
Říp -
Milešovka - 837

The name field persists and is rendered even if vyska is null. But there is no way to obtain this behavior from label expression dialog in qgis. I thik it would be nice to be possible enter fully user customized sql query for posgis layer in this label expression dialog, or add some choice to selectable string operators (coalesce should solve this issue too)

I hope, my contribution was intelligible despite my limited english abilities, and there is no duplicate.

thanks for reading, pk

History

#1 Updated by Jürgen Fischer over 7 years ago

  • Tracker changed from Bug report to Feature request

Looks like a feature request. But not sure - which feature is desired: PostGIS doesn't have concat. We have, but it returns NULL, if any of it's argument is NULL - but that's what I'd expect from it.

Be have also coalesce - so you could do name || ' - ' || coalesce(vyska,''). And we have CASE, so you could also do name || CASE WHEN vyska IS NULL THEN '' ELSE ' - '||vyska END.

#2 Updated by karpi lastname over 7 years ago

Hello,

Thanks for your response, it is really feature request more then bug as you explained, but I am confused in this:

-- "PostGIS doesn't have concat."

Exactly postgis doesn't, but postgresql does:

karpidb3=> \\df concat()
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+--------+------------------+---------------------+--------
pg_catalog | concat | text | VARIADIC "any" | normal
(1 row)

karpidb3=>
karpidb3=> SELECT version();
version
--------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.5 on i686-pc-linux-gnu, compiled by i686-pc-linux-gnu-gcc (Gentoo 4.5.3-r2 p1.4, pie-0.4.7) 4.5.3, 32-bit
(1 row)

-- "We have" ... "Be have also coalesce - so you could do ... coalesce(vyska,'')"

It is possible, I have some older version of qgis, - QGIS version 1.8.0-Lisboa - but if im trying enter concat() or coalesce(vyska,'') error "Expression is invalid" appears. The window 'More info on expression error' told:

Parser Error:
syntax error, unexpected '(', expecting $end

Eval Error:
No root node! Parsing failed?

-- Anyway. Your tip "name || CASE WHEN vyska IS NULL THEN '' ELSE ' - ' || vyska END" solved my difficulty and works ok, thanks. So the only feature request, which persist sounds: Clarify exactly which possibilities are allowed in label expression dialog (even for dumb user like me .) My first assumption is "I can enter here whatever sql query i want..." No, it is naturally not true. Then: "Ahh, I can enter only that formulas, that are proposed under my mouse" (but CASE is not there)

so thanks one more time and sory for my english.

#3 Updated by Jürgen Fischer over 7 years ago

  • Resolution set to worksforme
  • Status changed from Open to Closed

karpi lastname wrote:

karpidb3=> \\df concat()

ah, right. Ok, and I see that concat() doesn't return NULL, if there is a NULL argument. Maybe we should change our function.

"We have" ... "Be have also coalesce - so you could do ... coalesce(vyska,'')"

It is possible, I have some older version of qgis, - QGIS version 1.8.0-Lisboa - but if im trying enter concat() or coalesce(vyska,'') error "Expression is invalid" appears. The window 'More info on expression error' told:

Might be only in master.

Anyway. Your tip "name || CASE WHEN vyska IS NULL THEN '' ELSE ' - ' || vyska END" solved my difficulty and works ok, thanks. So the only feature request, which persist sounds: Clarify exactly which possibilities are allowed in label expression dialog (even for dumb user like me .) My first assumption is "I can enter here whatever sql query i want..." No, it is naturally not true. Then: "Ahh, I can enter only that formulas, that are proposed under my mouse" (but CASE is not there)

Apparently the expression builder in master was improved. It has the CASE case in the help tree.

Also available in: Atom PDF