COALESCE still returning null values
Marcee last edited by
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
COALESCE()when there was just
STRING_AGG(p.Label, ', ')before, and I am still getting
NULLinside comments. If I print Product_Comments before the update, I don't see any
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
COALESCEis not working somehow. How can I fix this?
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
) + pc.Comments from #Product_Comments pc.
If any part of the concatenate contains a
NULL, the whole result becomes
NULL. You need an additional
COALESCEaround the last reference to
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?