SQL View - add default values if null?

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

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 ...

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 ...

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

You can use IsNull(column_expression,default_value) function.

And why not define a default value on the field in the database table instead? Rememeber if you need all records to have this value or one submittedinthe insert or delete and you never want the value to be null, you must fix any exisiting nulls, and then add the default and make sure the field is set to not allow nulls. the advantage of this approach is it ensures that the rule will be followed no matter how the data is entered into the system. If you only want to use a default some of the time, do it inthe application code or stored proc. But if the field should alawys have a value, it is best to do it in the datbase to avoid data integrity issues.