jvoconnell
asked on
Using SELECT in CASE STATEMENT
I am having trouble getting a query to work. When I remove the SELECT subquery it runs. Can I do this in a CASE statement or is there a better way do achieve this?
An abbreviated version of the query is:
Select Member_ID, Date_of_Sale,
(CASE
WHEN REV_CD IS NOT NULL AND POS_CD IN ('02', '06', '07', '08', '09', '13', '14', '23') THEN '2'
WHEN PLAN_CD = 'HPHC' AND POS_CD IN ('MS', 'SC', 'AS', '11') THEN '2'
WHEN REV_CD IS NOT NULL AND PLAN_CD = 'TAHP' AND PROV_ID IN
(SELECT PROV_ID FROM HOSP) THEN '2'
ELSE '3'
END) TYPE_OF_SALE, SUM(ALL_AMT) ALL_AMT
From MySalesTable
Where Date_of_Sale between '01-JAN-07' and '31-DEC-09'
GROUP BY
Member_ID, Date_of_Sale,
(CASE
WHEN REV_CD IS NOT NULL AND POS_CD IN ('02', '06', '07', '08', '09', '13', '14', '23') THEN '2'
WHEN PLAN_CD = 'HPHC' AND POS_CD IN ('MS', 'SC', 'AS', '11') THEN '2'
WHEN REV_CD IS NOT NULL AND PLAN_CD = 'TAHP' AND PROV_ID IN
(SELECT PROV_ID FROM HOSP) THEN '2'
ELSE '3'
END)
I get a "not a group by expression" error. When I remove the sub-select part of the query, it runs ok. Any help is appreciated. Maybe not to difficult question for the Experts, but it's time sensitive. Thanks in advance
An abbreviated version of the query is:
Select Member_ID, Date_of_Sale,
(CASE
WHEN REV_CD IS NOT NULL AND POS_CD IN ('02', '06', '07', '08', '09', '13', '14', '23') THEN '2'
WHEN PLAN_CD = 'HPHC' AND POS_CD IN ('MS', 'SC', 'AS', '11') THEN '2'
WHEN REV_CD IS NOT NULL AND PLAN_CD = 'TAHP' AND PROV_ID IN
(SELECT PROV_ID FROM HOSP) THEN '2'
ELSE '3'
END) TYPE_OF_SALE, SUM(ALL_AMT) ALL_AMT
From MySalesTable
Where Date_of_Sale between '01-JAN-07' and '31-DEC-09'
GROUP BY
Member_ID, Date_of_Sale,
(CASE
WHEN REV_CD IS NOT NULL AND POS_CD IN ('02', '06', '07', '08', '09', '13', '14', '23') THEN '2'
WHEN PLAN_CD = 'HPHC' AND POS_CD IN ('MS', 'SC', 'AS', '11') THEN '2'
WHEN REV_CD IS NOT NULL AND PLAN_CD = 'TAHP' AND PROV_ID IN
(SELECT PROV_ID FROM HOSP) THEN '2'
ELSE '3'
END)
I get a "not a group by expression" error. When I remove the sub-select part of the query, it runs ok. Any help is appreciated. Maybe not to difficult question for the Experts, but it's time sensitive. Thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the responses. As I mentioned, this was just an abbreviated portion ofthe query. The entire process was run overnight. I tried cyberkiwi's suggestion and let the process run. After some QC, it was successful. I did not have to try the second suggestion, but I appreaciate the repsone. Thank you!!
select member_id, date_of_sale, type_of_sale, SUM(ALL_AMT) ALL_AMT
from
(
Select Member_ID, Date_of_Sale,
(CASE
WHEN REV_CD IS NOT NULL AND POS_CD IN ('02', '06', '07', '08', '09', '13', '14', '23') THEN '2'
WHEN PLAN_CD = 'HPHC' AND POS_CD IN ('MS', 'SC', 'AS', '11') THEN '2'
WHEN REV_CD IS NOT NULL AND PLAN_CD = 'TAHP' AND PROV_ID IN
(SELECT PROV_ID FROM HOSP) THEN '2'
ELSE '3'
END) TYPE_OF_SALE,
all_amt
From MySalesTable
Where Date_of_Sale between '01-JAN-07' and '31-DEC-09' ) x
group by member_id, date_of_sale, type_of_sale