Reports 2.5, how to do this please?

From: Belakimem <belakimem_at_aol.com>
Date: 27 Aug 1998 17:52:20 GMT
Message-ID: <1998082717522100.NAA12364_at_ladder03.news.aol.com>

[Quoted] Hi all, I am still frantically trying to learn Reports 2.5 / Oracle in general!

[Quoted] I was thinking to myself what relatively complicated thing could I do in Informix that would be nice to know how to do in Oracle?

So, a simple example/thought :

table

---

my_table

columns

---

customer
year
period
sales

(possibility of multiple entries for same period)

A report showing sales in a year across periods for a customer.

Year : xx

                     period

customer 1 2 3 .......... 12 total

IBM        29    0   56            3  535
dec        24   15   40            0  648

total      xx   xx   xx            xx xxx

In Informix I would have done it like this :

declare a cursor for

select customer, year, period, sum(sales) from my_table where year = 97
group by customer, year, period
order by customer, year, period

create a temporary table temp_table ( ** do they exist in oracle? ** )

with columns

customer,period_1_sales,period_2_sales......,period_12_sales

and add an index to the customer field

then I would read through the cursor in a loop until notfound

and before every group of customer I would insert a row into temp_table with [Quoted] values of customer and zeros for all the sales figures.

for every row in the cursor I would update the corresponding customer row in [Quoted] [Quoted] the temporary table with the appropriate sales figure for that period. Sometimes there would be no sales for that period and the column would remain [Quoted] zero. There would only be one update per column because of the grouping.

After processing the cursor I would then generate a report, as above, based on the temporary table.

Obviously a nice addition would be to be able to enter a year as a run time parameter.

Phew!, get it? Now, how to do it in Oracle! I don't expect complete answers - [Quoted] just a few pointers.

Thanks :) Received on Thu Aug 27 1998 - 19:52:20 CEST