Concatenate columns and some with NULL value

Question

I’m making a select and concatenating the columns, when a column has value NULL (has no value ) the whole line is NULL follows an example:

select 'teste'||CONTROLE||','||CODIGO||','||DATA||','||HISTORICO from tabela

The results:

null
null
null
null
teste05,06501,2015-08-01,maria
null
null
null
teste09,01123,2015-08-01,ronaldo
null
null
null
teste13,07361,2015-08-01,tenório

it is known that only the last column can have value null, then I wanted that in the first line the result was as example below:

teste01,06501,2015-08-01,
teste02,07052,2015-08-01,
teste03,03574,2015-08-01,
teste04,04123,2015-08-01,
teste05,06501,2015-08-01,maria
teste06,...
teste07,...
teste08,...
teste09,01123,2015-08-01,ronaldo
teste10,...
teste11,...
teste12,...
teste13,07361,2015-08-01,tenório

The above example I can’t get, I just wrote in the same hand to show what I expected as a result because even if all the columns were NULL still I wanted a result with what I concluded ex: teste,,, if everything were null.

I’ve tried using one if, I tried a case and nothing. Ma documentation I just found something that doesn’t fit this case.

Answer 1

You need to use the function COALESCE(coluna, '')