Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> View creation in Oracle 8.0.5 database
I have created two views in my Oracle 8 database that take data from a
very large table and groups that data into time buckets and performs
calculations based on those time buckets.
The two views are almost identical except for one field in the table.
I am using these views in a Crystal report in an effort to find the net difference between the data from each view grouped into the time buckets I created.
I think a better method for completing this objective might be to create one view and calculate the net difference between the two sets of data within the view, instead of attempting to perform the calculation within my Crystal report.
Does anyone have suggestions on how to rewrite these two views into one view and to find the net difference between the two sets of data? Data set one TYPE=O and data set two TYPE=I. I need the net difference as follows: O-I=net.
Here is the first view:
CREATE OR REPLACE FORCE VIEW SWDATA.SW_INV_TRANS_INS_BY_WEEKS
(WEEK_INCR_INS, PART_ID_INS, TOT_QTY_INS, QTY_04_WEEKS_INS,
QTY_12_WEEKS_INS,
QTY_26_WEEKS_INS, QTY_52_WEEKS_INS)
AS
SELECT (TRUNC ( ( (TO_NUMBER (TO_CHAR
((inventory_trans.transaction_date
), 'J' ) ) ) + 1 ) / 7 ) ) "WEEK_INCR_INS", inventory_trans.part_id, SUM (inventory_trans.qty) "TOT_QTY_INS", SUM (DECODE (LEAST (GREATEST ((NEXT_DAY (SYSDATE - 7, 'SAT') - 27), inventory_trans.transaction_date ), (NEXT_DAY (SYSDATE - 7, 'SAT')) ), inventory_trans.transaction_date, inventory_trans.qty, NULL ) ) "QTY_04_WEEKS_INS", SUM (DECODE (LEAST (GREATEST ((NEXT_DAY (SYSDATE - 7, 'SAT') - 83), inventory_trans.transaction_date ), (NEXT_DAY (SYSDATE - 7, 'SAT')) ), inventory_trans.transaction_date, inventory_trans.qty, NULL ) ) "QTY_12_WEEKS_INS", SUM (DECODE (LEAST (GREATEST ((NEXT_DAY (SYSDATE - 7, 'SAT') - 181), inventory_trans.transaction_date ), (NEXT_DAY (SYSDATE - 7, 'SAT')) ), inventory_trans.transaction_date, inventory_trans.qty, NULL ) ) "QTY_26_WEEKS_INS", SUM (DECODE (LEAST (GREATEST ((NEXT_DAY (SYSDATE - 7, 'SAT') - 363), inventory_trans.transaction_date ), (NEXT_DAY (SYSDATE - 7, 'SAT')) ), inventory_trans.transaction_date, inventory_trans.qty, NULL ) ) "QTY_52_WEEKS_INS"
'SAT' ) - 363 AND NEXT_DAY (SYSDATE - 7, 'SAT') AND inventory_trans.warehouse_id = 'PLANT' AND inventory_trans.CLASS IN ('I', 'A') AND inventory_trans.TYPE = 'I'
), 'J' ) ) ) + 1 ) / 7 ) ), inventory_trans.part_id
WITH READ ONLY; GRANT SELECT ON SWDATA.SW_INV_TRANS_INS_BY_WEEKS TO SW_DATA_READER; Here is the second view:
CREATE OR REPLACE FORCE VIEW SWDATA.SW_INV_TRANS_OUTS_BY_WEEKS
(WEEK_INCR_OUTS, PART_ID_OUTS, TOT_QTY_OUTS, QTY_04_WEEKS_OUTS,
QTY_12_WEEKS_OUTS,
QTY_26_WEEKS_OUTS, QTY_52_WEEKS_OUTS)
AS
SELECT (TRUNC ( ( (TO_NUMBER (TO_CHAR
((inventory_trans.transaction_date
), 'J' ) ) ) + 1 ) / 7 ) ) "WEEK_INCR", inventory_trans.part_id, SUM (inventory_trans.qty) "TOT_QTY", SUM (DECODE (LEAST (GREATEST ((NEXT_DAY (SYSDATE - 7, 'SAT') - 27), inventory_trans.transaction_date ), (NEXT_DAY (SYSDATE - 7, 'SAT')) ), inventory_trans.transaction_date, inventory_trans.qty, NULL ) ) "QTY_04_WEEKS", SUM (DECODE (LEAST (GREATEST ((NEXT_DAY (SYSDATE - 7, 'SAT') - 83), inventory_trans.transaction_date ), (NEXT_DAY (SYSDATE - 7, 'SAT')) ), inventory_trans.transaction_date, inventory_trans.qty, NULL ) ) "QTY_12_WEEKS", SUM (DECODE (LEAST (GREATEST ((NEXT_DAY (SYSDATE - 7, 'SAT') - 181), inventory_trans.transaction_date ), (NEXT_DAY (SYSDATE - 7, 'SAT')) ), inventory_trans.transaction_date, inventory_trans.qty, NULL ) ) "QTY_26_WEEKS", SUM (DECODE (LEAST (GREATEST ((NEXT_DAY (SYSDATE - 7, 'SAT') - 363), inventory_trans.transaction_date ), (NEXT_DAY (SYSDATE - 7, 'SAT')) ), inventory_trans.transaction_date, inventory_trans.qty, NULL ) ) "QTY_52_WEEKS"
'SAT' ) - 363 AND NEXT_DAY (SYSDATE - 7, 'SAT') AND inventory_trans.warehouse_id = 'PLANT' AND inventory_trans.CLASS IN ('I', 'A') AND inventory_trans.TYPE = 'O'
), 'J' ) ) ) + 1 ) / 7 ) ), inventory_trans.part_id
WITH READ ONLY; GRANT SELECT ON SWDATA.SW_INV_TRANS_OUTS_BY_WEEKS TO SW_DATA_READER; Any suggestions are greatly appreciated. Received on Tue Sep 02 2003 - 10:56:27 CDT