Here is a quick tip that you can use in QGIS expressions, T-SQL, or even PostgresSQL.

Normally if you have a column that you need query on to find all the NULL or zeros you have to do something like this:

COLA IS NULL OR COLA = 0

Well that isn’t too bad. Sure yeah it’s fine for one column but what if you have three and you need to check them all together

(COLA IS NULL OR COLA = 0) AND (COLB IS NULL OR COLB = 0) AND (COLC IS NULL OR COLC = 0)

That is pretty long and gets hard to read pretty quick.

To cut this down we can use the coalesce function – T-SQL, PostgresSQL, QGIS Expression. The coalesce function returns the first non-NULL value out of an expression, or list of values. So if you do something like this:

coalesce(NULL, "A", 0)

You will get “A” because the first value is NULL. The function will just evaluate each value/expression until something turns up that isn’t NULL.

Using that logic we can replace the above function with the following:

coalesce(COLA, 0) = 0 AND coalesce(COLB, 0) = 0 AND coalesce(COLC, 0) = 0

To me that is a lot clearer and readable.


Filed under: qgis Tagged: qgis, sql

by Nathan at 12:38 PM under qgis , sql (Comments)