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
LVL 1
jvoconnellAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
You could subquery it and defer the group by...

select memberid, dateofsale, typeofsale, sum(all_amt) all_amt
from
(
select memberid, dateofsale, case....., all_amt
from mysalestable
where...
) SQ
group by memberid, dateofsale, typeofsale

The other thing that comes to mind is to use type_of_sale in the group by clause as the 3rd item, instead of the case
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try this to see if it works. I think it is the same idea which is even given by cyberwiki.

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
0
 
jvoconnellAuthor Commented:
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!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.