using cursors for row comparison

Question

using cursors for row comparison

SteveB-74825

Hall of Fame

Points: 3290
More actions
June 1, 2004 at 3:23 am

#162596

i have to write an sp that produces an audit report.
at present i have a curosr that is populated with all the audit information. the audit information comes back in groups tied by a transaction id. at present the cursor counts the number of groups (244 out of 869 rows).
wha i need to do is do a row by row comparison to find out which fields have changed. is there a quick way to do this ?
cheers
dbgeezer

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65143 More actions · Answer 1

Are you matching across groups (tran id) or within groups and how many columns are you differentiating. Can you post tables structures and query that produces the cursor.

Far away is close at hand in the images of elsewhere.
Anon.

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 2

i can't post the tables as my boss won't allow it.

at the minute i'm comparing 10 fields max in 4 didfferent cursors using temp vars and then comparing to the row in the cursor.

i've not used cursors much so didn't know if there was a way to caompare one row with another.

i'll have to do it the long way round

cheers

dbgeezer

David Burrows SSC Guru Points: 65143 More actions · Answer 3

If you are comparing data from 4 cursors to another cursor then there must be a common key, so join the data using the key and do the matching, why use cursors.

Much simplified example

create tablea (ID int, col1 int, col2 int, col3 int, col4 int)

create table1 (ID int, col1 int)

create table2 (ID int, col2 int)

create table3 (ID int, col3 int)

create table4 (ID int, col4 int)

select a.ID,

a.col1, t1.col1, (case when a.col1 = t1.col1 then 0 else 1 end) as [diff1],

a.col2, t2.col2, (case when a.col2 = t2.col2 then 0 else 1 end) as [diff2],

a.col3, t3.col3, (case when a.col3 = t3.col3 then 0 else 1 end) as [diff3],

a.col4, t4.col4, (case when a.col4 = t4.col4 then 0 else 1 end) as [diff4],

from tablea a

inner join table1 t1 on t1.ID = a.ID

inner join table2 t2 on t2.ID = a.ID

inner join table3 t3 on t3.ID = a.ID

inner join table4 t4 on t4.ID = a.ID

Far away is close at hand in the images of elsewhere.
Anon.

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 4

i'm using a cursor because (i had nothing to do with the audit data!) the front end returns say 8 rows of audit info but only 2 changes all linked by thwe same audit and transaction ids.

i need to cycle through the rows filtering off the stuff i don't need whilst still comparing the cols in the rows.

i'm hoping to extend the cursor to include the cols from the other tables that i need to take data from.

cheers

dbgeezer