Replace string in LISTAGG function with another string if it is null-CodePudding

I just want to have the message 'no actors' if the name is NULL. How can I do this?

SELECT length, title, LISTAGG(SUBSTR(first_name, 1, 1) || '. ' || last_name, ', ') 
                     WITHIN GROUP (ORDER BY last_name) AS ACTORS -- 'no actors' if first_name is NULL
FROM film INNER JOIN film_actor USING (film_id)
INNER JOIN actor USING (actor_id)
WHERE release_year = 1991
GROUP BY title, length
ORDER BY length DESC;

I have tried it with NVL in the LISTAGG function, but I cannot get rid of the point '.' of the concatenation. Is there another way to do this?

CodePudding user response：

Use a CASE expression:

SELECT length,
       title,
       LISTAGG(
         CASE
         WHEN first_name IS NOT NULL
         THEN SUBSTR(first_name, 1, 1) || '. ' || last_name
         ELSE 'no actors'
         END,
         ', '
       ) WITHIN GROUP (ORDER BY last_name)
         AS ACTORS -- 'no actors' if first_name is NULL
FROM   film
       INNER JOIN film_actor
       USING (film_id)
       INNER JOIN actor
       USING (actor_id)
WHERE  release_year = 1991
GROUP BY title, length
ORDER BY length DESC;

CodePudding user response：

You could use DECODE(), or NVL2():

SELECT length,
       title,
       DECODE (first_name,
           NULL, 'No actors',
           LISTAGG (...)) AS actors
...

SELECT length,
       title,
       NVL2 (first_name,
           LISTAGG (...),
           'No actors') AS actors
...