Grouping Question

DB: Oracle 8.0.5

I'm working with a medical database. My boss wants me to create a report that shows the total savings for each case as a single line item with a computed column that shows Return on Investment (ROI). The savings are stored in the database as multiple line items--a case might have one line item, or it might have 10.

The following SQL statement successfully groups the savings into one line for each case (limited to 8 specific members for testing purposes).

  SELECT "CCMSDBA"."MEMBER"."MEMBER_ID",


         "CASE_SAVINGS"."CASE_SEQ",

         "MEMBER"."MEMBER_FIRST_NAME",

         "MEMBER"."MEMBER_LAST_NAME",

         SUM ("CASE_SAVINGS"."ESTIMATED_SAVINGS") "Total Savings"
    FROM "CCMSDBA"."MEMBER",


         "CASE_SAVINGS",

   WHERE ( "MEMBER"."MEMBER_ID" = "CASE_SAVINGS"."MEMBER_ID" ) and
         ( ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1186398' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1205870' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1179697' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1200116' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1203894' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1204273' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1218363' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1219602' ) )
GROUP BY "CCMSDBA"."MEMBER"."MEMBER_ID",


         "CASE_SAVINGS"."CASE_SEQ",

         "MEMBER"."MEMBER_FIRST_NAME",

         "MEMBER"."MEMBER_LAST_NAME",

So far, so good. Now the monkey wrench. The ROI calculation requires me to retrieve the total cost for each case. When I add that to the SQL statement, the results are no longer grouped into a single line item for each case. Here is the SQL statement that includes the total cost for each case.

  SELECT "CCMSDBA"."MEMBER"."MEMBER_ID",


         "CASE_SAVINGS"."CASE_SEQ",

         "MEMBER"."MEMBER_FIRST_NAME",

         "MEMBER"."MEMBER_LAST_NAME",

         "CASES"."CASE_COST",

         SUM ("CASE_SAVINGS"."ESTIMATED_SAVINGS") "Total Savings"
    FROM "CCMSDBA"."MEMBER",


         "CASE_SAVINGS",

         "CASES"

   WHERE ( "MEMBER"."MEMBER_ID" = "CASE_SAVINGS"."MEMBER_ID" ) and
         ( "CASE_SAVINGS"."MEMBER_ID" = "CASES"."MEMBER_ID" ) and
         ( ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1186398' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1205870' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1179697' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1200116' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1203894' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1204273' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1218363' ) or
         ( "CCMSDBA"."MEMBER"."MEMBER_ID" like '1219602' ) )
GROUP BY "CCMSDBA"."MEMBER"."MEMBER_ID",


         "CASE_SAVINGS"."CASE_SEQ",

         "MEMBER"."MEMBER_FIRST_NAME",

         "MEMBER"."MEMBER_LAST_NAME",

         "CASES"."CASE_COST"

Am I doing this wrong, or is this a limitation of the GROUP BY clause? I'm trying to do this as a tabular report in InfoMaker 7 because I'm not too familiar with the other types reports. Would it be easier to accomplish this using another type of report? I've also tried several grouping approaches on the report design sheet and have been unsuccessful that way as well.

I'm starting to get little desperate and any help would be greatly appreciated.

--Tom

Sent via Deja.com
http://www.deja.com/ Received on Thu Feb 08 2001 - 21:47:26 CST