[Qgis-user] using nested case statements in labelling

[Régis Haubourg]

Hi,
you are simply facing the SQL standard behavior of the | operator when it
compares something to a NULL value, the whole result will be NULL.
In SQL, you can avoid that by wrapping each value in a "coalesce(myvalue,
'')" that will replace NULL by an empty string.

In QGIS, you can also use the "+" operator, and this one does not sends a
NULL like the pipe operator. Far more simple indeed, but less portable to
pure SQL .
Cheers
Régis


2016-12-21 3:33 GMT+01:00 damos <damien.stephens at gmail.com>:

> HI I am trying to do a nested case statement for a labelling task.
> I have a list of mines of varying sizes with different commodities and
> would
> like to label with the name of the mine and the amount of each commodity at
> the mine in a new line below the name. I only want to label the mines that
> I
> have decided are signifcant and for this I have made a new column in my
> table called significant.
> My difficulty is that not all mines have the same commodities, for example
> one mine has copper (Cu) and no lead(Pb), while another has both. yet
> another will only have Pb.
> i can easily label all of the sigificant mines and their copper amoutns
> with
> a suffix of kt Cu using this code:
>
> case when  "signifcant"  = 'yes' then  title( "Name" ) ||   '\n'   ||
>  case
> when "Cu_e_t" <1 then '' else round("Cu_e_t"/1000,0) || 'Kt Cu' end else
> null end
>
> however if I then try to add in the Pb amounts like this;
> case when  "signifcant"  = 'yes' then  title( "Name" ) ||   '\n'   ||
>  case
> when "Cu_e_t" <1 then '' else round("Cu_e_t"/1000,0) || 'Kt Cu' end ||
> case when "Pb_e_t" <1 then '' else round("Pb_e_t"/1000,0) || 'Kt Pb' end
> else null end
>
> I lose all of the Cu only mines and only get labels for those with Cu and
> Pb
> or just Pb.
> I understand that I am doing something wrong with the nested case
> statements, but cant figure out what.
>
> any help would be much appreciated.
> int the mean time I am going back to do the formula in excel where I am
> capable.
>
>
>
>
> --
> View this message in context: http://osgeo-org.1560.x6.
> nabble.com/using-nested-case-statements-in-labelling-tp5300779.html
> Sent from the Quantum GIS - User mailing list archive at Nabble.com.
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20161221/0ebc71d5/attachment.html>