Re: Check previous record for duplicate field and rename

On May 25, 2:26 am, Nickneem <nickn..._at_gmail.com> wrote:
> 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/thr...
>
> 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

You don't.

Why do you feel a 'need' to display, in a view, data which is not consistent with that in the base tables? This is not merely some aggregate you're computing, you're wanting to alter, in appearance, base table data.

I can't understand the purpose for this exercise.

David Fitzjarrell Received on Fri May 25 2007 - 11:41:12 CDT