Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Grouping records for reporting

RE: Grouping records for reporting

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Tue, 04 Mar 2003 12:04:27 -0800
Message-ID: <F001.0055FE87.20030304120427@fatcity.com>


Barbara - Since you haven't gotten any replies, consider this:

create table test (col1 number, col2 number)
/

insert into test values (100, 2)
/

insert into test values (200, 3)
/

select a.col2 "First", b.col2 "Second"
from test a, test b
where a.col1 = 100
  and b.col1 = 200
/

Sometimes this is efficient enough for your purposes, even though it is doing two queries.

-----Original Message-----
Sent: Tuesday, March 04, 2003 10:06 AM
To: Multiple recipients of list ORACLE-L

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.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 - 14:04:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US