Grouping records for reporting

From: Barbara Baker <barbarabbaker_at_yahoo.com>
Date: Tue, 04 Mar 2003 08:05:40 -0800
Message-ID: <F001.0055F9C4.20030304080540@fatcity.com>

OpenVMS 7.2-1
Oracle 7.3.4
Crystal 8.5

List:
We have a large work order (WO) table. It previously had a record containing fields
job_nbr, charg_code_1, charg_code_2, charg_amt1, charg_amt2

After major application change, charge codes and amts were removed from WO and placed in new table. New table has the fields

        Job_nbr, entry_nbr, code, amt
where the entry_nbr can be 1 thru 9 with 9 corresponding amounts (i.e., up to 9 records for a particularly job number)

Developers want to report data as they did previously, horizontally instead of vertically, where they report job_nbr, entry1, amt1, entry2, amt2, etc. (They're using Crystal, which is also limiting our ability to do anything useful.) (Of course, they're linking to several other tables.)

They came up with this view:

CREATE OR REPLACE VIEW ADMARC.WO_CHARG_VIEW AS SELECT job_nbr, adj_nbr_key,

MIN(DECODE(entry_nbr,1,code)) prod1,
SUM(DECODE(entry_nbr,1,amt)) amt1,
MIN(DECODE(entry_nbr,2,code)) prod2,
SUM(DECODE(entry_nbr,2,amt)) amt2,

. . . . .
MIN(DECODE(entry_nbr,9,code)) prod9,
SUM(DECODE(entry_nbr,9,amt)) amt9
FROM ADMARC.wo_charg
GROUP BY job_nbr, adj_nbr_key

This is a performance killer. (The group by is getting us)

Keeping in mind that this is a 7.3.4 database, does anyone have ideas about a better way to group the data into a pseudo-record for reporting?

Thanks for any ideas.

Barb

---

Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Barbara Baker
  INET: barbarabbaker_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Mar 04 2003 - 10:05:40 CST