OraFAQ Forum: SQL & PL/SQL » Re: Find record, that causes DUP_VAL_ON

Home » SQL & PL/SQL » SQL & PL/SQL » Re: Find record, that causes DUP_VAL_ON_INDEX

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Re: Find record, that causes DUP_VAL_ON_INDEX [message #39445]

Wed, 17 July 2002 11:24

Mike Muller
Messages: 4
Registered: July 2002

Junior Member

Well,

I was thinking about the option with the ...
select
pk into v_pk
from
test
where
uniq = v_uniq;

The problem is, that the select would be something like

select
b.adid into n_tmp
from
table1 a,
table2 b
where
NVL(a.PFID,0) = NVL(b.PFID,0) and
NVL(a.STCODE,' ') = NVL(b.STCODE,' ') and
NVL(a.SPCODE,' ') = NVL(b.SPCODE,' ') and
NVL(a.TITEL,' ') = NVL(b.TITEL,' ') and
NVL(a.ANREDE,' ') = NVL(b.ANREDE,' ') and
NVL(a.VORNAME,' ') = NVL(b.VORNAME,' ') and
NVL(a.NACHNAME,' ') = NVL(b.NACHNAME,' ') and
NVL(a.GEBURTSDATUM,to_date('31.12.3000','dd.mm.yyyy')) = NVL(b.GEBURTSDATUM,to_date('31.12.3000','dd.mm.yyyy')) and
NVL(a.GESCHLECHT,' ') = NVL(b.GESCHLECHT,' ') and
NVL(a.FAMILIENSTAND,' ') = NVL(b.FAMILIENSTAND,' ') and
NVL(a.ANZAHLKINDER,0) = NVL(b.ANZAHLKINDER,0) and
NVL(a.BERUF,' ') = NVL(b.BERUF,' ') and
NVL(a.STRASSE,' ') = NVL(b.STRASSE,' ') and
NVL(a.HAUSNR,' ') = NVL(b.HAUSNR,' ') and
NVL(a.PLZ,' ') = NVL(b.PLZ,' ') and
NVL(a.ORT,' ') = NVL(b.ORT,' ') and
NVL(a.LKZ,' ') = NVL(b.LKZ,' ') and
NVL(a.LAND,' ') = NVL(b.LAND,' ') and
NVL(a.FIRMA1,' ') = NVL(b.FIRMA1,' ') and
NVL(a.FIRMA2,' ') = NVL(b.FIRMA2,' ') and
NVL(a.TELEFON,' ') = NVL(b.TELEFON,' ') and
NVL(a.FAX,' ') = NVL(b.FAX,' ') and
NVL(a.MOBIL,' ') = NVL(b.MOBIL,' ') and
NVL(a.EMAIL,' ') = NVL(b.EMAIL,' ') and
NVL(a.URL,' ') = NVL(b.URL,' ') and
a.adid = n_adid_i;

And that just takes ages, as u probably can imagine :)

So, the unique index in that case is not the pk. It goes over the columns in the select above...

Any other ideas??

greetz
mike

Report message to a moderator

Previous Topic:	defining number
Next Topic:	Median

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 02:31:37 CDT 2024