SQL View - add default values if null?

Question

I'm generating a view, and I want to populate cells with a pre-defined value if they are null.

The select for the view is:

SELECT a_case.Id, 
    R1.Type AS Referred_by_1,
    R1.Type AS Referred_by_2,
    R1.Type AS Referred_by_3
FROM dbo.CaseInfo a_case LEFT JOIN
    dbo.Referrer R1 ON P.Id = R1.Case_Id AND R1.Seq = 1 LEFT JOIN
    dbo.Referrer R2 ON P.Id = R2.Case_Id AND R2.Seq = 2 LEFT JOIN
    dbo.Referrer R3 ON P.Id = R3.Case_Id AND R3.Seq = 3 

The referrers are optional, and if not specified, I need to populate the field with 'ND'.

I think I maybe should be using CASE WHEN, but I'm not sure how to integrate that into the existing select...

Any advice gratefully received! - L

Answer 1

You can use ISNULL:

SELECT a_case.Id,      
    ISNULL(R1.Type, 'ND') AS Referred_by_1,     
    ISNULL(R2.Type, 'ND') AS Referred_by_2,     
    ISNULL(R3.Type, 'ND') AS Referred_by_3 
FROM ...

Answer 2

Use COALESCE e.g.

SELECT a_case.Id, 
       COALESCE(R1.Type, 'ND') AS Referred_by_1,
       COALESCE(R2.Type, 'ND') AS Referred_by_3,
       COALESCE(R3.Type, 'ND') AS Referred_by_3
  FROM ...

Answer 3

case when R1.Type is null then 'ND' else R1.Type end AS Referred_by_3