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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
