August 10, 2012 at 8:53 am
Hello,
I’d like to confirm if what I am attempting is possible. I suspect it’s not, but wanted to ask. I am trying to re-write a query that was given to me. The current version has lots of unions, and there are just minor variations in each select statement.
This is the new version I am attempting. As you can see, REASON_CODE 181 is used three times in the same CASE statement. In the original query, it appeared once in each union. I am trying to combine as many of the unions as possible to make this query simpler to read and maintain.
When I combine these into one CASE statement, I don’t get the same count as the original query. I suspect it’s because SQL can only count the REASON_CODE one time. Is there any way to get this to run in a single CASE statement?
CASE
WHEN REASON_CODE = 181 and CALLER_TYPE = 74 THEN 'Sending Letter'
WHEN REASON_CODE = 181 THEN 'Lost/Stolen Card'
WHEN REASON_CODE in (181,188) THEN 'Card Replacement'
ELSE 'OTHER' END AS REASON,
COUNT(*)
August 10, 2012 at 8:59 am
Sector7G (8/10/2012)
Hello,I’d like to confirm if what I am attempting is possible. I suspect it’s not, but wanted to ask. I am trying to re-write a query that was given to me. The current version has lots of unions, and there are just minor variations in each select statement.
This is the new version I am attempting. As you can see, REASON_CODE 181 is used three times in the same CASE statement. In the original query, it appeared once in each union. I am trying to combine as many of the unions as possible to make this query simpler to read and maintain.
When I combine these into one CASE statement, I don’t get the same count as the original query. I suspect it’s because SQL can only count the REASON_CODE one time. Is there any way to get this to run in a single CASE statement?
CASE
WHEN REASON_CODE = 181 and CALLER_TYPE = 74 THEN 'Sending Letter'
WHEN REASON_CODE = 181 THEN 'Lost/Stolen Card'
WHEN REASON_CODE in (181,188) THEN 'Card Replacement'
ELSE 'OTHER' END AS REASON,
COUNT(*)
Your CASE statement is fine except it will never reach WHEN REASON_CODE in (181,188) for cases where REASON_CODE = 181, as it will be handled by the previous WHEN REASON_CODE = 181 THEN 'Lost/Stolen Card'.
Now about count, until you post original and changed query it will be impossible to guess what is wrong.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply