Comparing 2 records (URGENT!!!!!)

[knaik]

Hi
I have a table containing more than 50 columns.

The data in it is as somewhat as below
BI- is before image
AI is after image
col1, col2, col3 .............................
BI .... ..... .............................
AI .... ..... .............................

BI .... ..... .............................
AI .... ..... .............................

BI .... ..... .............................
AI .... ..... .............................


I want to compare the record BI with its subsequent AI record.
Could anyone please lte me know ,how to compare each and every column in a record in a procedure. (I know that the 2 records cannot be tested for equality)

Please Help!

Thanks,




----------------

Thanks Marist89

But there is no key column for this table. There is one column called company . So for company =10 there are 100 rows , with 50 BI's and for each 50 BI's there are 50 AI's

[Edited by knaik on 08-06-2001 at 04:40 PM]

[marist89]

Hopefully, one of your columns is a join column. If so, you can do something like:

SQL> create table aud_hist (type varchar2(2), key_val number(10), data1 varchar2(10));

Table created.

SQL> insert into aud_hist values ('ai', 1, 'data1');

1 row created.

SQL> insert into aud_hist values ('bi', 1,
2 'data2');

1 row created.

SQL> commit;
...stuff deleted while I corrected my errors...
SQL> l
1 select a.key_val, decode(A.data1, b.data1, 'match','no-match') does_data_match
2 from aud_hist a, aud_hist b
3 where a.key_val = b.key_val
4 and a.type = 'ai'
5* and b.type = 'bi'
SQL> /

KEY_VAL DOES_DAT
---------- --------
1 no-match

[kmesser]

The problem lies in two key statements from your question:

1. "compare . . . subsequent . . . "

Subsequent has no meaning in Oracle without some column or set of columns with which to order the rows. How can we compare? Is it ok to compare ANY AI with ANY BI row with the same company number? If not, the game is over. It can't be done unless there is some additional info about a key column that you have not yet reported. Sorry.

2. " . . . there is no key column for this table . . . "

See #1 above.

Can you provide more info on what is an acceptable processing algorithm?

[knaik]

Originally posted by kmesser
The problem lies in two key statements from your question:

1. "compare . . . subsequent . . . "

Subsequent has no meaning in Oracle without some column or set of columns with which to order the rows. How can we compare? Is it ok to compare ANY AI with ANY BI row with the same company number? If not, the game is over. It can't be done unless there is some additional info about a key column that you have not yet reported. Sorry.

2. " . . . there is no key column for this table . . . "

See #1 above.

Can you provide more info on what is an acceptable processing algorithm?




Thanks all
since the table from where I am selecting the data is in db2. In DB2 , the before image(before update) row is always followed by the after image (after update).
And I am writing a procedure in Oracle and getting the data from db2 thru' Oracle transparent gateway.
There fore, I wrote cursor where it retrieves the data.
Cursor c1 is select * from table_a where col1 ='BI' or col1='AI';
I opened the cursor.since cursor picks up the row by row.
For c1_rec in c1 loop
IF col1='BI'
I am storing all the data in variables.
v_col2:= c1_rec.col2;

else
--here it comes for next interation (for all AI's)
IF (v_col2= c1_rec.col2)
then DBMS_OUTPUT.PUT_LINE('match');
else DBMS_OUTPUT.PUT_LINE('change');
end loop;

---And this works


Knaik