Newbie -- db reporting help!

From: Eric Raskin <eraskin_at_paslists.com>
Date: Fri, 3 Dec 1999 17:08:30 -0500
Message-ID: <s4gfpf2hgsp72@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 Fri Dec 03 1999 - 16:08:30 CST