Hi Set,

Yup that was it. Somewhere along the line the indexes had been lost on
that machine!!!! How the heck can that happen? And does that mean one
should check from time to time?

Rob


Svein Erling Tysvær wrote:

> What's the PLAN on remote 2.0.3? Just wondering if this is a huge table and indexes on Name or CompanyName are missing (it has to go NATURAL on C, but should use at least one index for C2).
>
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Robin Davis
> Sent: 24. oktober 2007 16:44
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] Why has this dupe search statement stopped working over TCP/IP?
>
> Hi Set,
>
> Thank you for your more refined query, it works on 2.0.1 remote and
> 2.0.3 local just fine. However, the same problem exists with remote 2.0.3.
>
> Very strange.
>
> Rib Davis
>
>
> Svein Erling Tysvær wrote:
>
>> Are you sure it works on a local machine? I wasn't aware that you could compare EXISTS with true/false (it may be possible, I've just never seen that before). Try this "simplified" statement:
>>
>> SELECT c.CONTACTID, c.NAME, c.COMPANYNAME, c.ADDRESS1, c.ADDRESS2,
>> c.ADDRESS3, c.ADDRESS4, c.POSTCODE, c.WORKPHONE, c.WORKEXTENSION,
>> c.MOBILEPHONE, c.EMAILNAME
>> FROM Contacts AS c
>> WHERE Exists (select * from Contacts c2
>> where c2.Name = c.Name
>> and c2.CompanyName = c.CompanyName
>> and NOT (c2.ContactID = c.ContactID))
>> ORDER BY 2, 1;
>>
>> HTH,
>> Set
>>
>> -----Original Message-----
>> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Robin Davis
>> Sent: 24. oktober 2007 16:07
>> To: firebird-support@yahoogroups.com
>> Subject: [firebird-support] Why has this dupe search statement stopped working over TCP/IP?
>>
>> Hi,
>>
>> I'm obviously losing my marbles somewhere, but I seem to have the
>> strangest problem.
>>
>> Using a 2.0.1 remote server on XP, this query works fine.
>>
>> SELECT c.CONTACTID, c.NAME, c.COMPANYNAME, c.ADDRESS1, c.ADDRESS2,
>> c.ADDRESS3, c.ADDRESS4, c.POSTCODE, c.WORKPHONE, c.WORKEXTENSION,
>> c.MOBILEPHONE, c.EMAILNAME
>> FROM Contacts AS c
>> WHERE (((Exists (select * from Contacts c2
>> where c2.Name = c.Name
>> and c2.CompanyName = c.CompanyName
>> and NOT (c2.ContactID = c.ContactID)))<>False))
>> ORDER BY 2, 1;
>>
>> Using the same query on a remote 2.0.3 server, latest release, the query
>> stalls and returns no data. The really weird thing is, it DOESN'T cauase
>> a problem on a local 2.0.3 server, only on remote.
>>
>> Anybody any clues as to where I should start?
>>
>> Thanks in advance,
>>
>> Rob Davis
>>