Solved

Using SELECT in CASE STATEMENT

Posted on 2010-11-22
3
821 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:jvoconnell
3 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34192991
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34193723
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
 
LVL 1

Author Closing Comment

by:jvoconnell
ID: 34198786
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

30 Experts available now in Live!

Get 1:1 Help Now