hi all
thanx for all those who contributed...
my prob is solved now..
special thanks to rudy zung.. thanks again
Shirish Khapre, SE Rolta India Ltd.
Off Ph No. (+91) (022) 832,826,8300568
Ext'n 2730
Minds are like parachutes. They only function when they are open
--
Please see the
Title: RE: DUPLICATE VALUE CHECK
is it
possible in oracle 8.0.5?
shirish
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Koivu, LisaSent:
Wednesday, June 27, 2001 12:37 AMTo: Multiple recipients of list
ORACLE-LSubject: RE: DUPLICATE VALUE
i am getting duplicate records if the whole (say XYZ_A_LO_001) value
is repeated without much probs..
but my prob is checking duplication of the last 7 digits..
Shirish Khapre, SE Rolta India Ltd.
Off Ph No. (+91) (022) 832,826,8300568
Ext'n 2730
Minds are like parachutes. They
Not ins are nasty, try something more like:
create table tmp (
dup_valule varchar(100),
keep_row rowid);
create index t1 on tmp(dup_value);
insert into tmp
select my_column_name, max(rowid) from my_table
group by my_column_name
having count(*) 1);
select my_table.*
from my_table, tmp
where
How about:
create table tmp_table as select substr(rowid,length(rowid)-7,7) last_seven
from your_table;
and then:
select last_seven duplicates from
(
select last_seven,count(*)
from tmp_table
group by last_seven
having count(*) 1
);
I didn't test it, but it should be pretty fast with only
Shirish,
If the last 7 digits of this column has to be unique throughout the table,
why not, upon insert, move this value to another column in the table and
place a unique key on that column? This will prevent the record from being
inserted.
CREATE OR REPLACE TRIGGER TEST_TRIGGER
BEFORE
Title: RE: DUPLICATE VALUE CHECK
Hi Shirish, FWIW:
Function-based index? Reverse index? for case II?
Have you considered either of these options to ease the pain of that query?
HTH
Lisa Koivu
Clumsy Ninja-ette (WA!)
Ft. Lauderdale, FL, USA
-Original Message-
From
