COALESCE still returning null values



  • 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?



  • 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?




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2