Re: newbie question, unique index || double entries

From: <fgreene_at_hayes.com>
Date: 27 Aug 93 10:20:06 EDT
Message-ID: <7859.2c7ddfd6_at_hayes.com>

In article <19930826110203.nussbaumer_at_fennec.unibe.ch>, nussbaumer_at_130.92.4.10 (Marc Nussbaumer) writes:
> Hi,
> I would like to create a unique index on two numerical fields in
> a table which has about 20'000 records.
> I did a
> SELECT COUNT(NO1||'.'||NO2)
> FROM ...
> WHERE ...
> and a
> SELECT COUNT(UNIQUE(NO1||'.'||NO2))
> FROM ...
> WHERE ...
> Now the first SELECT reports 20'000
> and the second reports 19'999
> which means that I have _one_ DOUBLE ENTRY and my Index won't work!
> My question is, how do I spot the double entry?
> BIG thanks for your answer
> Ma
>
>
> -----------------------------------------------------------------------------
> Marc A. Nussbaumer, Ph.D., Natural History Museum, Bernastrasse 15
> CH-3005 Bern, Switzerland, Phone +41 (0)31 482290, FAX +41 31 482499
> nussbaumer_at_nmbe.unibe.ch
> -----------------------------------------------------------------------------

Using your original example, just combine the two queries as in

 SELECT COUNT(NO1||'.'||NO2)
 FROM ...
 WHERE ...

INTERSECT                                 <------------------ 

 SELECT COUNT(UNIQUE(NO1||'.'||NO2))
 FROM ...
 WHERE ... This will return only the records that satisfy both searches and, hence, are the duplicate records.

---

 | Frank Greene DELPHI SYSTEMS Inc    |          _/_/_/  _/_/_/             |
 | Telephone [615] 458-6032           |           _/_/    _/_/              |
 | Internet 74200.427_at_compuserve.com  |          _/_/    _/_/  _/_/_/       |
 | Compuserve 74200,427               |         _/_/    _/_/    _/_/        |
 | 324 Ootsima Way                    |        _/_/    _/_/    _/_/         |
 | Loudon, TN 37774                   |       _/_/_/  _/_/_/  _/_/_/        |
 ----------------------------------------------------------------------------
 |         Of course, any opinions or suggestions are strictly my own       |
 ----------------------------------------------------------------------------

Received on Fri Aug 27 1993 - 16:20:06 CEST