CODEWITHSUNDEEP
databasedb2listagg
Andrew123
Andrew123

Reputation: 33

Manage null values in LISTAGG

I have a table of Policies and a table of Customers. For each policy I can have many customers. What I want to do is wrap the customers data in one record grouped by PolicyId.

So this is my query:

SELECT p.Id AS POLICY, LISTAGG(c.Name || ',' || c.Address || ',' || c.Phone || '||') AS CUSTOMERS
FROM Policies p
INNER JOIN Customers c ON c.PolicyId = p.Id
WHERE p.Id = 'AXSD1234'
GROUP BY p.Id

The problem I have is that if one of the customer fields is null, the whole value retrieved in CUSTOMERS string becomes null as well. So if I have a Customers table like the following:

+----+----------+------+------------+-------+
| Id | PolicyId | Name |  Address   | Phone |
+----+----------+------+------------+-------+
|  1 | AXSD1234 | Jhon | Street 123 |  1234 |
|  2 | AXSD1234 | Anna | NULL       |  2345 |
+----+----------+------+------------+-------+

I would expect to retrieve something like this:

+----------+------------------------------------+
|  POLICY  |            CUSTOMERS               |
+----------+------------------------------------+
| AXSD1234 | John,Street 123,1234||Anna,,2345|| |
+----------+------------------------------------+

But instead I get this:

+----------+-----------+
|  POLICY  | CUSTOMERS |
+----------+-----------+
| AXSD1234 | NULL      |
+----------+-----------+

Is there anyway to prevent this from happening other than using NVL or the like on each field of the LISTAGG?

Maybe there is a completely different approach to this?

Thank you in advance for reading.

Upvotes: 0

Views: 1573

Answers (1)

Paul Vernon
Paul Vernon

Reputation: 3901

COALESCE is the only way.

SELECT p.Id AS POLICY, LISTAGG(COALESCE(c.Name,'') || ',' || COALESCE(c.Address) || ',' || COALESCE(c.Phone) || '||') AS CUSTOMERS
FROM Policies p
INNER JOIN Customers c ON c.PolicyId = p.Id
WHERE p.Id = 'AXSD1234'
GROUP BY p.Id

If you have a lot of columns, I guess you could generate your statements from the system catalog to save some typing

Upvotes: 3

Related Questions