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

Home -> Community -> Usenet -> c.d.o.server -> View creation in Oracle 8.0.5 database

View creation in Oracle 8.0.5 database

From: Jason <jsmith_at_swanstone.com>
Date: 2 Sep 2003 08:56:27 -0700
Message-ID: <bb9ee7e0.0309020756.4e9b4a40@posting.google.com>


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"

    FROM sysadm.inventory_trans inventory_trans    WHERE inventory_trans.transaction_date BETWEEN NEXT_DAY (SYSDATE - 7,
                                                              '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'

GROUP BY (TRUNC ( ( (TO_NUMBER (TO_CHAR
((inventory_trans.transaction_date
                                           ),
                                           '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"

    FROM sysadm.inventory_trans inventory_trans    WHERE inventory_trans.transaction_date BETWEEN NEXT_DAY (SYSDATE - 7,
                                                              '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'

GROUP BY (TRUNC ( ( (TO_NUMBER (TO_CHAR
((inventory_trans.transaction_date
                                           ),
                                           '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

Original text of this message

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