Home » RDBMS Server » Server Administration » Re: Rows in one table not in another -Real Urgent
| Re: Rows in one table not in another -Real Urgent [message #373805] |
Thu, 10 May 2001 16:20 |
Cindy
Messages: 88
Registered: November 1999
|
Member |
|
|
OR Try this:
There are several ways you can do this: First method). you can create a Unix script wrap around SQL that extract all data from table A in a data file that are not on table B. In addition, you need to create a control file for table B, if it doesn't aready exist, and load script(using sqldr --SQL loader-- to load data into table B) . (same method can apply to your second question select data from table B with the condition of Column D is different for a given combination of colA and colB). Second method) write two procedures, one for first question and the other for second question, that do the tasks for you. In the procedure you need to include all fields name on "DECLARE" part. (I am assuming that Table A fields are identical to table B for question number one or Table A contains all fields of Table B plus more, but you only need to include all fields name of table B). If this is one time situation (you only have to do this once to fix existing data), then I would use the procedure method to handle these tasks.
For Example:
SET SERVEROUTPUT ON
spool file_name.dat /* Purpose: Just for verification to see what data are being selected before insecting*/
DECLARE
v_a B.a%TYPE;
v_b B.b%TYPE;
v_c B.c%TYPE;
v_d B.d%TYPE;
.
.
.
so on and so on ultil all colums needed is define here.
CURSOR C1 IS
SELECT A.a,
A.b,
A.c,
A.d,
. ,
. ,
. ,
. /* so on and so on */
FROM A a
WHERE (a.a, a.b, a.c, a.d) NOT IN (select b.a, b.b, b.c, b.d
from B b);
/* You shouldn't run into cartesian product with the above select statement, however if you do, then substitute this select statement with Andrews's select statement using MINUS */
BEGIN
OPEN C1;
LOOP
FETCH C1 into v_a,
v_b,
v_c,
v_d,
/* so on and so on */
. ,
. ,
. ,
. ;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_a || v_b || v_c || v_d || and so on and so on ); /* Just print output on screen to */
INSERT into B(a, b, c, d, .....)
VALUES (v_a, v_b, v_c, v_d, ....);
END LOOP;
CLOSE C1;
END;
/
spool off
show error
For part two question: Just modify the "DECLARE" part so that all fields are in Table C only and change the select statement to this:
select v_a, v_b, v_d, and so on and so on
from B b
where b.D <> (select sum(b.a+b.b)
from B b
and condition or conditions if needed, if not then end from);
Then modify the "FETCH" part to include all fields define from "DECLARE" as suggested above (in part two question:) and change your insert statment accordingly. Also, I am assuming column c, column a, and column b are numeric values.
NOTE: You need to put a COMMIT statement after insert, after you verify that correct data and no more errors found, before you want place commit statement to insert statement for table B and table C.
HTH
--Cindy
|
|
|
|
Goto Forum:
Current Time: Tue Jul 02 17:05:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
