Duplicate check
IDS: 9.4FC3
O/S: HPUX 11.11
I am trying to determine the best query to use for a view showing only
duplicate records.
fields in view char(1),smallint,char(2),int, date (which together are
the pk of table) I'll call these fields 1-5
Duplicate checking fields are 1st 4 from pk (which identify a particular
case) and another int (field 6) that holds a action code.
I want to check a single case for a duplicate of the action code and
return the case and dates of occurrences.
Query I have now is:
select 1,2,3,4,5
from table1 x0
where EXISTS (select 1,2,3,4,6
from table1 x1 where x1.1= x0.1AND x1.2= x0.2AND x1.3= x0.3
AND x1.4= x0.4AND x1.6= x0.6
group by 1,2,3,4,6
having count(*) > 1 AND x0.6= value I am dupe checking for);
Thanks,
Randy
You can also do a self join set the primary keys equal and the non key
fields not equal call these non key fields f9, f11, f12
select
a.*,
b.*
from
table1 a,
table2 b
where
a.k1 = b.k1 and
a.k2 = b.k2 and
a.k3 = b.k3 and
(a.f9 <> b.f9 or a.f11 <> b.f11 or a.f12 <> b.f12)
I don't think this is any faster.
You can use PDQPRIORITY to make yours faster.
Oh, and never select anything in your exists
exists(select * from table1 where something = somethingelse)
Informix probably knows to ignore the fields you are selecting, but I
think other database used to key off of the * as a special case with
exists. It let the optimizer choose any path.
Kennedy, Randy wrote:
> IDS: 9.4FC3
> O/S: HPUX 11.11
>
> I am trying to determine the best query to use for a view showing only
> duplicate records.
>
> fields in view char(1),smallint,char(2),int, date (which together are
> the pk of table) I'll call these fields 1-5
>
> Duplicate checking fields are 1st 4 from pk (which identify a particular
> case) and another int (field 6) that holds a action code.
>
> I want to check a single case for a duplicate of the action code and
> return the case and dates of occurrences.
>
> Query I have now is:
>
> select 1,2,3,4,5
> from table1 x0
> where EXISTS (select 1,2,3,4,6
> from table1 x1 where x1.1= x0.1AND x1.2= x0.2AND x1.3= x0.3
> AND x1.4= x0.4AND x1.6= x0.6
> group by 1,2,3,4,6
> having count(*) > 1 AND x0.6= value I am dupe checking for);>
> Thanks,
> Randy