| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Grouping records for reporting
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,
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
-- 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
 |
 |