My latest attempt at finding duplicates also doesn't work (returns no records) but should return at least 288

SELECT
_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))
FROM
Career
JOIN Contacts C ON _fkContacts = C._ID
WHERE
CAST(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16)) AS VARCHAR(80))
IN
(
SELECT
CAST(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16)) AS VARCHAR(80))
FROM
Career
JOIN Contacts C ON _fkContacts = C._ID
GROUP BY _Surname,_Forename,_fkCompanies,_fkSites
HAVING COUNT(_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))) > 1
)

This one I think should work. The subselect returns 144 records.

This

SELECT
_fkContacts,
_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))
FROM
Career
JOIN Contacts C ON _fkContacts = C._ID
WHERE
_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))
=
'AllisonAnthony10000638493'

works but this

SELECT
_fkContacts,
_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))
FROM
Career
JOIN Contacts C ON _fkContacts = C._ID
WHERE
_Surname + _Forename + CAST(_fkCompanies AS VARCHAR(16)) + CAST(_fkSites AS VARCHAR(16))
IN
('AllisonAnthony10000638493')

doesn't
Roy Lambert

Roy,

Weird, I just fixed this one right before I started looking at this - it was
reported about a week ago by Piotr Font.  The issue is with EDB properly
detecting that you're mixing columns from different tables in the WHERE
clause, and appropriately resorting to doing a row scan in such a case.
2.03 B8 or earlier is trying to apply the WHERE clause to a specific table,
and that's not correct.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy

You can eliminate the IN clause altogther but I suspect it might be a tad
slow <bg>

SELECT DISTINCT _fkContacts,_Surname, _Forename,_fkCompanies,_fkSites FROM
Career C
JOIN Contacts C1 ON C._fkContacts=Contacts._ID
JOIN
(SELECT _Surname, _Forename,_fkCompanies,_fkSites,count(*) as cnt
FROM Career
JOIN Contacts C ON _fkContacts = C._ID
GROUP BY _Surname, _Forename,_fkCompanies,_fkSites
HAVING cnt > 1) t1
ON C1._Surname=t1._Surname AND C1._Forename= t1._Forename AND
C._fkCompanies=t1._fkCompanies AND
C._fkSites=t1._fkSites


John

Tim

>Weird, I just fixed this one right before I started looking at this - it was
>reported about a week ago by Piotr Font. The issue is with EDB properly
>detecting that you're mixing columns from different tables in the WHERE
>clause, and appropriately resorting to doing a row scan in such a case.
>2.03 B8 or earlier is trying to apply the WHERE clause to a specific table,
>and that's not correct.

So to ask the famous question - when's 2.03b9 out?

Roy Lambert

John


Wrong - it returned very quickly, but produced zero records

Roy Lambert

Roy

>
> Wrong - it returned very quickly, but produced zero records
>


Would it be possible to post a script to create a career and contact table
with data which exhibits the problem?  I created a table with 4 records and
it produced the expected result.  If you have data which doesn't work it may
be showing a different problem.

Cheers

John

Roy Lambert wrote:

> So to ask the famous question - when's 2.03b9 out?

"By this weekend, at the latest" :-/

John


If you want the intellectual challenge I'll be happy to create the bumph for you, but I've decided on a different approach. I need manual intervention anyway so I can use the initial query to get what I need on a duplicate by duplicate set basis and present that to the use for decision.

Thanks for the help so far and if you want the script just say.

Roy Lambert

Roy,

<< So to ask the famous question - when's 2.03b9 out? >>

It looks like by this evening (EST), so tomorrow morning for you.

--
Tim Young
Elevate Software
www.elevatesoft.com

<< "By this weekend, at the latest" :-/ >>

You do realize that customers can send us new bug reports that end up
delaying these builds, don't you ?  The testing and build process is
sufficiently long enough now to warrant holding off new builds if I know I'm
just going to have to do another build soon thereafter.

I just had it happen today - I'm about ready to start the testing for B9,
and I just got a new bug report via email.  Now I've got to stop what I'm
doing, find out what the issue is, and then pick up where I left off.  This
happens quite often.

--
Tim Young
Elevate Software
www.elevatesoft.com