How to use case instead of IsNull

Question

I have the below query to be re-written without using the IsNull operator as I am using the encryption on those columns and IsNull isn't supported.

Case When Indicator = 'N' Then Null 
Else IsNull(c.email1, IsNull(E.email, ORG_Email)) End EmailAddress

If you have more than two things to compare and pick the first non-null, you can use [coalesce](https://msdn.microsoft.com/en-us/library/ms190349.aspx) instead of calling `isnull` multiple times. Not sure if that would help you here though. — Joe Enos, Aug 02 '16 at 21:38

score 2 · Answer 1 · answered Aug 02 '16 at 21:40

2

I would suggest coalesce() for this purpose:

(Case When Indicator = 'N' Then Null
      Else coalesce(c.email1, E.email, ORG_Email)
 End) as EmailAddress

But I would phrase this without the else. Assuming Indicator is never NULL:

(case when Indicator <> 'N' 
      then coalesce(c.email1, E.email, ORG_Email)
 end) as EmailAddress

answered Aug 02 '16 at 21:40

Gordon Linoff

1,242,037
58
646
786

If `Indicator` might be null, check this: http://stackoverflow.com/questions/10416789/how-to-rewrite-is-distinct-from-and-is-not-distinct-from – Kamil Gosciminski Aug 02 '16 at 21:42

score 1 · Accepted Answer · answered Aug 02 '16 at 21:40

1

Here's how you would do it with a case statement:

Case 
    when Indicator = 'N' then Null 
    when c.email1 is not null then c.email1 
    when e.email is not null then e.email 
    else ORG_Email
end EmailAddress

However as others have pointed out, another option would be to use coalesce if that'll work for your needs. Not sure why isnull isn't supported.

answered Aug 02 '16 at 21:40

sgeddes

62,311
6
61
83

In the above condition what happens if the Indicator != 'N' ? – HadoopAddict Aug 02 '16 at 21:53
@HadoopAddict -- this should be equivalent to your existing statement. If indicator != 'N', then it checks to see if the `c.email1` field is not null. If not, select that, if null, check the next -- basically think of this as `if else if`... – sgeddes Aug 02 '16 at 21:55
or in the above answer did you forget to use else at the end of each 'then' statement ? – HadoopAddict Aug 02 '16 at 21:56
@HadoopAddict -- when using `case`, you can have multiple `when then` clauses. As I mentioned, think of it as `if, else if, else if, else`. You can string as many as you need. – sgeddes Aug 02 '16 at 21:58
Great ! I really want to use Case statements to achieve the result. But How can I solve this part of problem. Can I use any operator instead of LEN ? Len(IsNull(c.email1, IsNull(e.email, ORG_Email)) ) <> 0 – HadoopAddict Aug 02 '16 at 22:56
@HadoopAddict -- not sure I'm following but that sounds like a different question. Probably best to post a new question for such. – sgeddes Aug 02 '16 at 23:11
asked here : http://stackoverflow.com/questions/38744997/replacing-isnull-and-len-with-case-and-is-null – HadoopAddict Aug 03 '16 at 13:28

John Bollinger · Answer 3 · 2016-08-02T21:52:37.680

I have the below query to be re-written without using the IsNull operator as I am using the encryption on those columns and IsNull isn't supported

I suppose you mean that columns c.email1 and E.email are never null, but they sometimes contain encrypted strings that convey the absence of data in a manner similar to that ordinarily conveyed by a NULL. For example, maybe the encrypted form of an empty string is used. In that event, you can compare their values to the encrypted null-equivalent:

Case
  When Indicator = 'N' Then Null 
  when c.email1 != 'some-encrypted-string-equivalent-to-null' then c.email1
  when E.email != 'some-encrypted-string-equivalent-to-null' then E.email
  else ORG_Email
End EmailAddress

Of course, that assumes that there is a single null-equivalent string, at least on a per-column basis, which might not be the case. If it isn't then the only options I see are

decrypt the e-mail values in the query (possibly via a user-defined function) and write the conditions based on the decrypted values, or
return both the encypted c.email1 and E.email as separate columns, or null for both if Indicator is 'N', and select which one to use on the application side, after decryption.

How to use case instead of IsNull

3 Answers3