November 10, 2015 at 11:32 am
Hi members,
I am trying to add a second condition in my case stmt that compares value in PC_PHYSICIAN and provider_id of most recent record based on max(last)updt_dt) values in 2 separate tables and when they are equal it should return 15 when not equal 20. I have written below code. Since its is part of a store proc I am forced to split the case. Is there any way to do this.
select I.app_id,
case TRANSINT.ACTION_TYPE
WHEN 'R' AND EXISTS (SELECT 1 FROM tab2 I
inner join tab1 TRANSINT
on I.pid = TRANSINT.pid and I.provider_id = TRANSINT.PC_PHYSICIAN and I.LAST_UPDATE_DATE =
(SELECT MAX(LAST_UPDATE_DATE) FROM tab2 D WHERE D.pid = I.pid))
THEN '15'
else '20'
end
from tab2 I WITH(NOLOCK)
inner join tab1 TRANSINT WITH(NOLOCK)
on I.pid = TRANSINT.pid
Thanks in advance.
November 10, 2015 at 11:38 am
I'm still looking at the query to find a better way, but the "simple" answer is that you can't do:
CASE [column] when [value] and [other condition]
You have to do it this way:
CASE WHEN [column] = [Value] AND [other condition] THEN [Result] ELSE [RESULT] END
Can you post table definitions, sample data, and expected results as noted in the first link in my signature? This will help us provide a better solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2015 at 2:13 pm
After reformatting your code, the subquery in the CASE is independent of the outer query. To demonstrate that, I renamed the aliases used in the subquery based on how SQL looks at the code. How do you want to relate the subquery to main query?
select
I.app_id,
case TRANSINT.ACTION_TYPE
WHEN 'R' AND EXISTS (SELECT 1
FROM
tab2 I1
inner join tab1 TRANSINT1
on (I1.pid = TRANSINT1.pid and
I1.provider_id = TRANSINT1.PC_PHYSICIAN and
I1.LAST_UPDATE_DATE = (SELECT MAX(D.LAST_UPDATE_DATE)
FROM tab2 D
WHERE D.pid = I1.pid)))
THEN '15'
else '20'
end
from
tab2 I -- WITH(NOLOCK)
inner join tab1 TRANSINT -- WITH(NOLOCK)
on I.pid = TRANSINT.pid;
November 10, 2015 at 3:50 pm
While there's nothing wrong with the above approaches functionally, you're not doing any favors to anyone who has to read this code after you. If this was me, I would pre-create a variable with meaning like @ProviderIsMostRecent and set it based on your join query result.
That way, your CASE says something like
CASE WHEN ACTION_TYPE = 'R' and @ProviderIsMostRecent = TRUE Then 15 ELSE 20 END
which you (or others) will understand later without having to dig out your notes
November 11, 2015 at 7:36 am
Also a bit off topic but I notice you using the NOLOCK hint on your queries. This hint is very often used incorrectly because the person writing the code does not really understand that hint. You might take a look at this article and then decide if that hint is really a good idea in your situation.
http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply