Re: Sorting on a calculated field

From: Andre Vergison <avergison_at_infosoft.be>
Date: 1995/12/06
Message-ID: <4a3qj1$e2o_at_news.Belgium.EU.net>#1/1

garagan_at_ug.cs.dal.ca (Sean Garagan) wrote :

> I am currently using Reportwriter 2.0 (soon to upgrade to 2.5) and I am
> having a problem with one report I have just written. My employer has asked
> for the final summary of the total report to be sorted on a percentage that
> is calculated from 2 other fields, also calculated fields. The overall
> report is grouped on a sub-table of the main table. It looks like the
> following:
>
> Drug class Drug type Cost Percentage
>
> Percentage is calculated from (Cost / overall total cost)
>
> Cost is calculated from several fields in the main table grouped by drug type
>
> Drug class is not in the main table, it is in a sub-table containing the
> drug info
>
> Reportwriter doesn't seem to have anyway of telling it to sort on a calculated
> field, or to put this information into a table or cursor at runtime to
> allow a second query to sort that table.
>
> I have just started using Oracle and SQL in the last 6 weeks, so it's
> probably something small that I am missing.
>
> Thanks in advance,
>

Maybe you can solve your problem with SQL.

First, obtain the overall-total. Say this is stored in GENTOT.

Then, the following query would return the information you want:

select DI.drug_class, MT.drug_type,

       sum( MT.costcol1 + MT.costcol2 + MT.costcol3 ) TYPECOST
       to_char( sum( MT.costcol1 + MT.costcol2 + MT.costcol3 )
                * 100 / :GENTOT,
                '990.00') perc

 from drug_info DI, main_table MT
 where DI.keycolumn = MT.drug_info_column  group by DI.drug_class, MT.drug_type
 having sum( MT.costcol1 + MT.costcol2 + MT.costcol3 ) <> 0  order by TYPECOST;

Gerard H. Pille, Infosoft, ghp_at_infosoft.be Received on Wed Dec 06 1995 - 00:00:00 CET