![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 30 total |
![]() |
Tue, Mar 9 2010 1:45 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Wed, Mar 10 2010 12:20 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Wed, Mar 10 2010 1:11 PM | Permanent Link |
John Hay | 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 |
Thu, Mar 11 2010 4:29 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Thu, Mar 11 2010 4:44 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | John
Wrong - it returned very quickly, but produced zero records ![]() Roy Lambert |
Thu, Mar 11 2010 5:44 AM | Permanent Link |
John Hay | 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 |
Thu, Mar 11 2010 5:48 AM | Permanent Link |
Gienek Majonez | Roy Lambert wrote:
> So to ask the famous question - when's 2.03b9 out? "By this weekend, at the latest" :-/ |
Thu, Mar 11 2010 12:07 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Thu, Mar 11 2010 12:29 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Thu, Mar 11 2010 12:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | << "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 |
Page 1 of 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |