Problem with SQL*ReportWriter

I am having a problem with SQL*ReportWriter v.1.1.12 (a pathetic program at best, but all we've got).

Basically, there are three queries involving 4 main tables, each with a summary value (sum) calculated on a field. Table A (a list of cases) connects, in turn, to Table B (a list of visits by date), to Table C (a list of medications by date), and to Table D (a list of lab tests by date). Query 1 (linking A to B) prints down. Query 2 (linking A to C) and query 3 (linking A to D) print to the right. The summaries are based on the total cost for visits, meds, and lab tests during the query time frame. Thus, the report looks like this:

     Name                           Visits     Meds    Labs   Totals
     XXXXXXXXX                       99.99    99.99   99.99   $99.99
     etc.
     Grand Totals                   $99.99   $99.99  $99.99   $99.99

I cannot simply join all four tables, because some cases do not appear at all in one or more of the other tables, and some cases appear in the tables, but not with dates inside the parameters. My attempt to create a multiple outer join resulted in a Cartesian join in which many of the totals in the tables were repeated. I have structured the queries so that the same complete list of cases appears for each query by using unions, nvl(), and "where not exists." Therefore, the initial query printed down, and the subsequent queries printed across, line up exactly.

Everything works fine except for the "Totals" column. I cannot find a way to recover the summary value for "Visits" and "Meds" when creating a field for the line total, and assigning it to the "Labs" group. I attempted to simply insert "&TVISIT+&TMED+&TLAB" in the Labs group footer (where all the totals are printed since the individual field values are not printed), but received an error message. I created a field with a source of "&sql select :visit+:med+:lab into :GStotal from dual" and created a summary from this and got a Totals column reflecting only the Labs totals. I cannot figure out how to access the previous summary values in a subsequent group printing.

I apologize for the sketchy and incomplete nature of this description, but I am wondering if I am overlooking something basic, or easy, or if I have missed some pearl in the wonderful(!) Oracle manuals.

Thanks very much in advance

Paul Larned
larned_at_uhunix.uhcc.hawaii.edu Received on Fri Aug 27 1993 - 04:11:13 CEST