Make your own free website on Tripod.com

InstantDB Project
About InstantDB
Project Mail Lists
Short History
Reporting Bugs
Screen Shots
3rd Party Examples
FAQs

Software
Downloads
Documentation
CVS Repositories
Roadmap
License

About Enhydra.org
Who We Are
News, Articles & Events
Getting Involved
Contact Us

Community
Demos
Contributions
Resources
Case Studies
On The Edge! -NEW-
Commercial Vendors


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Test for duplicates


Peter...

Microsoft Access uses a technique like

SELECT Name FROM Phonelist As Tmp GROUP BY Name Having COUNT(*) > 1

to find duplicates, triplicates,... in a table.

The whole query for a phonelist table is actually

SELECT Name, Phone, Loc
FROM Phonelist
WHERE Name In (SELECT Name FROM Phonelist As Tmp GROUP BY Name HAVING
Count(*)>1 )
ORDER BY Name

I tried both queries with InstantDB version 3.10 and JDK 1.1.8 with
Swing  and each gives the following basic error in my application's
logfile:

Attempted query - SELECT Name FROM Phonelist As Tmp GROUP BY Name Having
COUNT(*) > 1
AWT-EventQueue-0 SELECT Name FROM Phonelist As Tmp GROUP BY Name Having
COUNT(*) > 1
AWT-EventQueue-0 java.lang.ArrayIndexOutOfBoundsException: 2 >= 2
java.sql.SQLException: 2 >= 2

I think the problem may be with InstantDB Syntax not allowing a
search_condition such as COUNT(*) > 1.
I know when I replaced the "> 1" with a "> 2" I got an identical error.

Is this something you could fix, or is this not a compliant SQL query,
and rather a Microsoft Access extension?

I do know that this is a very useful query for finding errors in data
entry.

Regards, -JohnT.

To unsubscribe from this list, please an send
email to 'majordomo@smartcard.co.uk' with the text
'unsubscribe instantdb' in the message body.