Re: Matrix Reports

In article <1993Jun03.183047.17903_at_ncrcae.ColumbiaSC.NCR.COM> rgass_at_yesac.ColumbiaSC.NCR.COM (Roy.Gass) writes:
>Is there a simple way to produce trend matrix reports (for lack
>of a better term) in SQL. SQL*ReportWriter enables simple
>schemes, but I want a little more abstraction.
>
> For example: (Product order rate)
>
> 01/93 02/93 . . NN/NN
>
> Product A
>
> Product .
>
> Product X
>

I don't know whether this answer applies at all in Oracle, but with Informix SQL I sometimes do something like this:

Suppose the table is "sale" :

   product         month           quantity
   -------         -----           --------

     A               1               150
     A               3               175
     B               1               20
     B               4               10

The problem arises when there are certain products which didn't sell at all in certain months, so any SELECT you do on this cursor will not bring back anything corresponding to that month and that product. So rather than a getting a "0" in the appropriate cell in the matrix, you might well find yourself putting subsequent values into the wrong cell in the matrix (e.g. everything that follows the "missing cell" being "off by one").

I get round this like this:

select unique product from sale into temp all_products;

select unique month from sale into temp all_months;

select all_products.products, all_months.month, 0 qty   from all_products, all_months
  into temp frame_table;

[Now the temporary table "frame_table" looks like this:

   product         month           quantity 
   -------         -----           -------- 

      A              1                0
      A              2                0
      A              3                0

and has an entry for *every* combination of months-and-products, even if that particular month-and-product was missing from the "sale" table.]

Then:

select product, month, quantity
  from sale
 UNION
select product, month, quantity
  from frame_table
  into temp another_temp_table;

select product, month, sum(quantity)
  from another_temp_table
 group by 1,2
 order by 1,2

This final select you can make into a cursor and have it hand the rows it fetches off to a report, or whatever. The point is that this select will do the same thing as

select product, month, quantity
  from sale

*except* that it will also fetch

  "A", 2, 0

if it is the case that we sold none of product A in February.

I am not at all sure if I answered your question, or another question that used to float around in my head (and having solved it, I want to tell everyone!). Anyway, just a suggestion, for what it is worth.

Paul Received on Sun Jun 06 1993 - 21:20:53 CEST