I have created two identical tables which list all the exceptions with regards to certain rules of products in the data base. table one is for week 1 table two is for week two the exception are just view on a weekly basis and no correction is made in the data base. week twos data includes the exceptions in week one i want to exclude week ones exceptions from week twos. only to view the new exceptions
+1
A:
I suggest you add a timestamp to the tables and select/filter on this timestamp.
This way you also can put all exceptions into one table and just define two views.
I'm no good with PL/SQL, but maybe you can adapt my MySQL-sample:
CREATE DATABASE timetest;
CONNECT timetest;
CREATE TABLE errorlog (
stamp TIMESTAMP NOT NULL ,
error VARCHAR(255) NOT NULL
);
INSERT into errorlog (`stamp`, `error`)
VALUES (DATE_SUB(CURDATE(),INTERVAL 8 DAY), 'old');
INSERT into errorlog (`stamp`, `error`)
VALUES (NOW(), 'new');
SELECT * FROM errorlog WHERE stamp>DATE_SUB(CURDATE(),INTERVAL 7 DAY);
SELECT * FROM errorlog WHERE stamp<DATE_SUB(CURDATE(),INTERVAL 7 DAY);
DROP DATABASE timetest;
Gives me:
mysql> SELECT * FROM errorlog WHERE stamp>DATE_SUB(CURDATE(),INTERVAL 7 DAY);
+---------------------+-------+
| stamp | error |
+---------------------+-------+
| 2009-01-29 01:44:38 | new |
+---------------------+-------+
mysql> SELECT * FROM errorlog WHERE stamp<DATE_SUB(CURDATE(),INTERVAL 7 DAY);
+---------------------+-------+
| stamp | error |
+---------------------+-------+
| 2009-01-21 00:00:00 | old |
+---------------------+-------+
Leonidas
2009-01-29 00:35:35
i have a time stamp in both tables, the exceptions are not changed in the data base it is still pulls week1 data into week two data i thought that if i created 2 tables one with week 1 data and one with week two data i could then create a third table and some how remove week one from week two data?
2009-01-29 00:52:49
Leonidas is right, this should be one table.
Mark Nold
2009-01-29 01:31:52
if i do this as one table how would i remove the data for the first week that has already appeared on the report
2009-01-29 02:44:59
I don't really understand your last question. By "select"ing only the data for timestamps in the last 7 days (first query) you do not have the data for the fiurst week in the result (only showing "new" values). To remove old entries, just "delete" where "stamp>DATE_...." as in the "select".
Leonidas
2009-01-29 12:49:52
A:
Oracle has a MINUS operator so you can do
SELECT col_a, col_b... FROM table_new
MINUS
SELECT col_a, col_b... from table_old
That works as long as the columns have corresponding datatypes (so will work with SELECT * if the tables have identical structures).
Gary
2009-01-29 00:53:28
A:
There also may be some advantage to breaking the table into partitions by year/week number.
EvilTeach
2009-01-29 01:51:48
A:
To really help you we need more information. Are you talking about tables or views ("... the exception are just view on a weekly basis.. " part confused me)?
If you could provide a short complete example (yes - stolen from Tom Kyte) we could provide a complete answer. That is if the above have not helped you.