Check previous record for duplicate field and rename

I have a query which creates a view with a field which is 99.8% unique (DEBTRANS.VOUCHER) SELECT TRIM(DEBTRANS.ACCOUNTNUMBER) AS INDBDebnmbr
, TRIM(DEBTRANS.VOUCHER) AS INinvoicenmbr
, DEBTRANS.DATE_ AS INinvoiceDate
, DEBTRANS.DUEDATE AS INinvoiceDueDate
, DEBTRANS.TXT AS INDescription
, DEBTRANS.EXCHANGECODE AS INCurrencyCode
, DEBTRANS.AMOUNTMST AS INOriginalamount
, DEBTRANS.SETTLEAMOUNTMST AS INpaidAmount
, (DEBTRANS.AMOUNTMST - DEBTRANS.SETTLEAMOUNTMST)AS INOpenAmount
   FROM XAL_SUPERVISOR.DEBTRANS DEBTRANS WHERE DEBTRANS.OPEN = 1 AND    DEBTRANS.TRANSTYPE <> 9 AND
   (DEBTRANS.AMOUNTMST - DEBTRANS.SETTLEAMOUNTMST) <> 0 AND    DEBTRANS.DATASET = 'FIK' In rare occasions there are say 3 records with the same debtrans.voucher, what I want is that the second record with the same debtrans.voucher gets a -1 or just 1 added. I saw some posts about decode or CASE but I'm not really an Oracle expert.

This one comes close:

http://groups.google.nl/group/comp.databases.oracle/browse_thread/thread/7f16aecd43762155/7bb8e89feb858a7a?lnk=st&q=sql+check+previous+record+duplicate+oracle&rnum=5&hl=nl

declare
 prevrec itable%ROWTYPE;
for irec in (select * from itable order by field_a, field_b, ... ) loop
 if prevrec.field_a <> irec.field_a
 or prevrec.field_b <> irec.field_b
 or ... then /* if one of the fields differs;   insert into otable values (irec.field_a, irec.field_b, ...);   prevrec.field_a := irec.field_a;
  prevrec.field_b := irec.field_b;
  ... /* save all fields that have to be compared */ end loop;

But how would I do such a thing in a create view statement?

Mike Received on Fri May 25 2007 - 02:26:30 CDT