COALESCE still returning null values

Question

UPDATE #Product_Comments
  SET #Product_Comments.Comments = (
      SELECT COALESCE(STRING_AGG(p.Label, ', '), '') + ' '
      from (
        select distinct Label, Product_ID
        from #TEMPORARYTB
        where Product_ID = pc.Product_id
      ) as p  
      GROUP BY p.Product_ID
  ) + pc.Comments from #Product_Comments pc
JOIN Product_Property p ON p.Product_ID = pc.Product_id

I used COALESCE() when there was just STRING_AGG(p.Label, ', ') before, and I am still getting NULL inside comments. If I print Product_Comments before the update, I don't see any NULL inside comments.

SELECT *, '4' AS debug FROM #Product_Comments

I am printing it like this. So, when I do

SELECT *, '3' AS debug FROM #Product_Comments

I don't see any NULL, which is weird. It means that the COALESCE is not working somehow. How can I fix this?

Answer 1

In addition to what Akina said about your subquery possibly returning an empty rowset, which will produce a NULL. You're also turning around and concatenating the result of the SELECT right back to a potential NULL with ) + pc.Comments from #Product_Comments pc.

If any part of the concatenate contains a NULL, the whole result becomes NULL. You need an additional COALESCE around the last reference to pc.Comments.

Try this and see if it works. It's pretty rudimentary, but I only had a few minutes to type it up. It should point you in the right direction.

SET #Product_Comments.Comments = COALESCE((
    SELECT STRING_AGG(p.Label, ', ')
    FROM (
        SELECT distinct Label, Product_ID
        FROM #TEMPORARYTB
        WHERE Product_ID = pc.Product_id
        ) as p  
    GROUP BY p.Product_ID
    ),'') + ' ' + COALESCE(pc.Comments,’’)
FROM #Product_Comments pc
JOIN Product_Property p ON p.Product_ID = pc.Product_id

Of course, I'd also have ask if you really need to concatenate comment back to itself in the first place?