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

Klaus Byskov Pedersen · Accepted Answer · 2011-02-10 11:47:15Z

27

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

answered Feb 10, 2011 at 11:47

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Damien_The_Unbeliever Over a year ago

@Lainie - you should be aware that ISNULL is SQL Server dialect - if you ever work on a different database system, it won't be there. COALESCE is standard (and extends to more than 2 arguments, gets datatypes right, etc)

Klaus Byskov Pedersen Over a year ago

@Damien_The_Unbeliever ISNULL is also supported in MySql. The Oracle equivalent is NVL and SqlLite has IFNULL. You are correct that COALESCE is standard and could be used just as well. COALESCE also supports more than one parameter to check for null, so it is more powerful.

onedaywhen · Accepted Answer · 2011-02-10 11:50:56Z

23

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

answered Feb 10, 2011 at 11:50

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Comments

x2. · Accepted Answer · 2011-02-10 11:49:01Z

4

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

answered Feb 10, 2011 at 11:49

x2.

9,6586 gold badges43 silver badges63 bronze badges

1 Comment

jzd Over a year ago

Why would you want to use a case statement instead of ISNULL or COALESCE?

KV Prajapati · Accepted Answer · 2011-02-10 11:47:30Z

2

You can use IsNull(column_expression,default_value) function.

answered Feb 10, 2011 at 11:47

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Comments

HLGEM · Accepted Answer · 2011-02-10 16:10:51Z

0

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.

answered Feb 10, 2011 at 16:10

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Collectives™ on Stack Overflow

SQL View - add default values if null?

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related