May 6, 2003 at 5:33 pm
Hi,
What I'm trying to do here is:
1) Find all rows (in table 1) that were modified yesterday
2) Of those rows returned, join them to table 2 and compare specific attributes in them.
3) Return those rows which have discrepancies and send them in an email
I'm stuck on the comparing part. I have created a cursor to hold the list of row_ids that returned from table 1 where the modified time was yesterday.
Inside the cusor, I need to somehow compare these columns between 2 tables joining by the row_id. However, I have no idea how to do it! Can anyone shred some light on how I should go about doing this? Thanks!!
Nick
May 6, 2003 at 6:02 pm
insert into ##temptable
select relevant.columns
from table1 inner join table2 on pk1 = pk2
where table1.createdate = yesterday
and (table1.col1 <> table2.col1 or
table1.col2 <> table2.col2 or
... )
You will then have all of the data needed for your email easily accessable.
May 8, 2003 at 3:30 pm
OK, I'm finally done w/ it. The previous solution using WHERE t1.col1 <> t2.col1 OR t1.col2 <> t2.col2... is not good. In that SELECT statement, I'll be listing out all the columns from both tables if one of those columns are mismatched. That means I'll have to go into that result one more time to filter out where the real errors occur.
So, here's the persudo(?) codes of what I've done:
1) SELECT COUNT(*) of rows modified yesterday.
2) IF COUNT(*) = 0 THEN QUIT
3) DECLARE variables for all attributes
4) DECLARE modified_cursor CURSOR
5) SELECT all relevant attributes from table 1 into modified_cursor
6) OPEN CURSOR, FETCH NEXT FROM modified_cursor INTO variables
7) WHILE LOOP
8) IF @col1 <> (SELECT col1 from table 2), INSERT INTO ##tempTable
9) repeat IF statement and go through all variables <-> columns
10) FETCH NEXT FROM modified_cursor INTO variables
11) back to step 7) WHILE LOOP
12) CLOSE and DEALLOCATE CURSOR
Now, I'm actually creating a table so instead on INSERT INTO a ##temptable, I'll record these daily errors permanently. Since I'm inserting data one row at a time, I thought it's a good idea to use Explicit Transactions. Problem is, I'm not sure how to use TRANSACTIONS with CURSORS, and where to put the BEGIN, ROLLBACK and COMMIT TRAN. Can anyone help? Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply