Difference between these Case statements
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
#2 uses and
#3 -- uses or
#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#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#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 = @BusinessNameIdASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>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.
Not correct. It DOES exit out of the CASE at the first WHEN..THEN line that evaluates to true.
ASKER
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.OutgoingPatientToProvi
Is there anyway to do this in Case?
So, I have 2 tables, I'm left joining and some rows belong to dbo.OutgoingPatientToProvi
Is there anyway to do this in Case?
ASKER
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.
Maybe I need to replace the Case with Select and select from those 2 tables and not use Case. I'll try it.
>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.OutgoingPatientToProvi
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...
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.OutgoingPatientToProvi
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
Thanks for the grade and the article upvote. Good luck with your project. -Jim
ASKER
Thanks for the article