Today I learned that you essentially cannot concatenate (concat, ||) nullable columns properly in DB2/SQL.
To be honest, I should have know this of course.
I have a table that records money spent, with a date, text, account and a physical location, e.g. a town, district, ... indicating where the money was spent.
It is nullable because not all transactions need to have a place recorded.
For some statistical analysis I wanted to create one column with text and account and the location simply concatenated into one, like account||' '||text||' '||location.
Turns out, then the location is NULL the whole string will turn NULL
Which reminded me - painfully, after about 30min - that NULL is not a value. You cannot do anything with NULL.
So you have to cast it away to a default value, e.g. with coalesce(location,'dummylocation').
Sigh.
Subscribe