Re: Help with Report Design

[Quoted] You are correct in identifying the problem of null entries for a zip. You have to have a single query that brings back the zips and the data has to be done with look ups.

I would create a function that would take a zip and a time period and return your data.
[Quoted] Then place the function in the DB and build a view that looks like what you need.
[Quoted] The you report becomes a simple single table report.

If you don't have a zip table then your main view should be

select distinct zip, p.f(zip,'last6') last6, p.f(zip, 'last6_5to49') last6_5to49, ...
from customer

By placing the function in the DB you can eliminate the awful network traffic.

Paul Dorsey
Dulcian, Inc.
(212) 595-7223
web address: http://www.dulcian.com
email: pdorsey_at_dulcian.com

Eric Raskin <eraskin_at_paslists.com> wrote in message news:s4isdjq5qeg13_at_corp.supernews.com...
> 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:
>
> ZIP Last 6 Mos Entire File Last 6 Mos $5-$49
Last
> 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
> 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
> 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 Sun Dec 05 1999 - 04:52:09 CET