Am concatenating multiple rows using : col1||col2.... but I am getting null as one of the columns

ahaldar1106

is a null value. how to get not null column

Jim_Knicely

Maybe the NVL function?

dbadmin=> SELECT * FROM t;
 a | b | c
---+---+---
 A |   | B
(1 row)

dbadmin=> SELECT a || b || c abc FROM t;
 abc
-----

(1 row)

dbadmin=> SELECT NVL(a, '') || NVL(b, '') || NVL(c, '') abc FROM t;
 abc
-----
 AB
(1 row)

See: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Null/NVL.htm

Bryan_H

COALESCE with blank. Replace null with your column name below:
dbadmin=> select 'foo'||COALESCE(null,'')||'bar';

?column?

foobar
(1 row)

ahaldar1106

It worked thanks.

Jim_Knicely

@ahaldar1106: @Bryan_H's suggestion might be the better choice as COALESCE is an ANSI SQL-92 standard!

dbadmin=> SELECT * FROM t;
 a | b | c
---+---+---
 A |   | B
(1 row)

dbadmin=> SELECT coalesce(a, '') || coalesce(b, '') || coalesce(c, '') abc FROM t;
 abc
-----
 AB
(1 row)

See: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Null/COALESCE.htm

marcothesane

COALESCE() might be a little slower - as it has a variable number of parameters, and returns the first non-null in the list.
IFNULL(), NVL() and ISNULL() are synonyms; all three take two arguments, and return the second argument if the first is NULL.

You can expect functions with a fix number of arguments to have a less complex context, and be slightly faster ...