CODEWITHSUNDEEP
sqlsql-server
HadoopAddict
HadoopAddict

Reputation: 225

How to use case instead of IsNull

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

Upvotes: 0

Views: 289

Answers (3)

John Bollinger
John Bollinger

Reputation: 181859

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

  1. decrypt the e-mail values in the query (possibly via a user-defined function) and write the conditions based on the decrypted values, or

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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

Upvotes: 2

sgeddes
sgeddes

Reputation: 62861

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.

Upvotes: 1

Related Questions