COALESCE function in OVER statement not working

Question

Can someone please tell me why the COALESCE is working on the first SELECT here and not the other two? I'm still getting NULL values on the second two statements.

(SELECT COALESCE(DEFax, NULL, '') FROM Debtor d WHERE d.DEIsPrimary = 1 AND d.CApKey = c.CApKey) AS FaxNumberOne,
        (SELECT COALESCE(DEFax, NULL, '') FROM (SELECT ROW_NUMBER() OVER (ORDER BY DEpKey ASC) 
            AS rownumber, DEFax FROM Debtor d WHERE d.CApKey = c.CApKey AND d.DEIsPrimary <> 1) 
            AS foo WHERE rownumber = 1) AS FaxNumberTwo,
        (SELECT COALESCE(DEFax, NULL, '') FROM (SELECT ROW_NUMBER() OVER (ORDER BY DEpKey ASC) 
            AS rownumber, DEFax FROM Debtor d WHERE d.CApKey = c.CApKey AND d.DEIsPrimary <> 1) 
            AS foo WHERE rownumber = 2) AS FaxNumberThree

Thanks!

Answer 1

Sample data and desired results would really help.

But a scalar subquery is a subquery that returns one column and zero or one rows. If it returns zero rows, then the value is NULL regardless of the expression in the SELECT. In other words, the COALESCE() needs to go outside, something like this:

coalesce( (select . . . . ),
          ''
        )

Including NULL in the coalesce() list is not a good practice. It is unnecessary and misleading -- and always ignored.