Difference between these Case statements

Camillia
Camillia used Ask the Experts™
on
I have 3 tables. I join them with left join. I want to bring back 'Y' for a certain condition from 2 tables. What's the difference between these 3 case statements.



#1 on separate lines
 case
     when opp.sendpatient = 1  then 'Y' 
     When omp.PatientFaxed = 1  then 'Y'
     else 'N' 
  end as referred

 
 FROM EMRFiles e
 left join dbo.OutgoingPatientToProviders opp on opp.EMRId = e.Id
 left join dbo.OutgoingNonMemberPatient omp on omp.emrid = e.Id
 
 where e.BusinessNameId = @BusinessNameId

Open in new window


#2 uses and

 case
     when opp.sendpatient = 1 and omp.PatientFaxed = 1  then 'Y' 
     
     else 'N' 
  end as referred

 
 FROM EMRFiles e
 left join dbo.OutgoingPatientToProviders opp on opp.EMRId = e.Id
 left join dbo.OutgoingNonMemberPatient omp on omp.emrid = e.Id
 
 where e.BusinessNameId = @BusinessNameId

Open in new window


#3 -- uses or
 case
     when opp.sendpatient = 1 or omp.PatientFaxed = 1  then 'Y' 
     
     else 'N' 
  end as referred

 
 FROM EMRFiles e
 left join dbo.OutgoingPatientToProviders opp on opp.EMRId = e.Id
 left join dbo.OutgoingNonMemberPatient omp on omp.emrid = e.Id
 
 where e.BusinessNameId = @BusinessNameId

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
1 - Separate WHEN..THEN statements will execute top-down and regardless of everything else in the flow.
   First line - referred will equal 'Y' if opp.sendpatient = 1, regardless of anything else.
   Second line - referred will equal 'Y' if omp.PatientFaxed = 1, regardless of anything else.
   Third line - If first and second lines do not evaluate to TRUE then third line will execute and referred will equal 'N'.

2 - Uses and AND in the same WHEN..THEN.  WHEN opp.sendpatient = 1 AND omp.PatientFaxed = 1
   So if one of these columns is 1 and the other is 0, the first CASE will set referred='Y', but the second case will not because they're not both = 1, so will set referred='N'.

3 - Effectively the same as the first CASE block.

btw I have an article out there called SQL Server CASE Solutions, which doesn't address this AND..OR situation directly, but I'll have to add it.

Hope this helps.
Jim
So for #1, all the lines will execute ...just from top down..correct? It won't exit out of the Case if the first line is matched...right?

Thanks for the article
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>It won't exit out of the Case if the first line is matched...right?
Not correct.  It DOES exit out of the CASE at the first WHEN..THEN line that evaluates to true.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

That's not what I want. I want to look at both columns. Should I be using the OR? is this logic totally wrong?!

So, I have 2 tables, I'm left joining and some rows belong to dbo.OutgoingPatientToProviders and some belong to dbo.OutgoingNonMemberPatient.

Is there anyway to do this in Case?
Yeah, your answer is correct but my logic is wrong. I don't think I should be using left joins and case.

Maybe I need to replace the Case with Select and select from those 2 tables and not use Case. I'll try it.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>I want to look at both columns. Should I be using the OR?
You'll have to tell us what you're trying to accomplish in order for us to say what you *should* do here.
Without that, all we can tell you is how T-SQL works.

>I'm left joining and some rows belong to dbo.OutgoingPatientToProviders and some belong to dbo.OutgoingNonMemberPatient ... Is there anyway to do this in Case?
That can happen in CASE, but keep in mind that if a row doesn't exist in the right table, then all right table columns will be NULL, and you'll have to capture that in any logic.  Something like...

SELECT CASE
  WHEN ISNULL(left_table.this, -1) =1 OR ISNULL(right_table.that, -1) =1 THEN 'Y'
  ELSE 'N' END as referred

Open in new window

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the grade and the article upvote.  Good luck with your project.  -Jim

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial