Help with Report Design
Date: Sat, 4 Dec 1999 14:56:21 -0500
Message-ID: <s4isdjq5qeg13_at_corp.supernews.com>
[Quoted] Help!!! I've got a master table like this:
create table customer(
id number,
name varchar2(30),
street varchar2(30),
city varchar2(20),
state varchar2(2),
zip varchar2(5)
);
.. and a detail table like this:
create table orders(
ord_id number, cust_id number,
ord_date date,
ord_amt number(9,2)
);
I need to get statistical counts for a report. It would look something like this:
[Quoted] ZIP Last 6 Mos Entire File Last 6 Mos $5-$49 Last [Quoted] 6 Mos $50-$99 Last 6 Mos $100+ 11111 123 40 55 28 11112 78 22 37 19
etc.
I'm planning on using Oracle Reports for this. The only thing I can think of is to write 4 queries (one per column) that look like this:
select a.zip, o.count(*)
from orders o, address a
where o.cust_id = a.id
and months_between(o.ord_date, sysdate) <= 6 and o.ord_amt between 5 and 49
group by a.zip;
This would generate the second column. The problem with this is coordinating the data in the rows. If a particular zip code has 0 in a column, it won't show up in the query at all. So, if I put the queries into [Quoted] 4 repeating columns down the page, the zips won't line up!!
What's the correct way to do this? (Short of writing a report in COBOL using procob)....
TIA
Eric Raskin
eraskin_at_paslists.com
P.S. There's actually more to the problem than this. I've simplified it as [Quoted] much as I can in order to make it coherent. :-) The customers have type codes that have to broken out as well, which will become different matrix groups (I hope). Received on Sat Dec 04 1999 - 20:56:21 CET