Re: Grouping Question

What are the primary keys for the tables? From what you said, I would guess that the CASES primary key is more than just the MEMBER_ID.

Al

On Fri, 09 Feb 2001 03:47:26 GMT, thomasm516_at_my-deja.com wrote:

>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 Fri Feb 09 2001 - 16:55:51 CST