Subject Re: Why does this query work with 1.53 but not 2 (Beta)?
Author Svein Erling Tysvær
Hi Robin!

First, a couple of points:

a) this is not yet a list for Firebird 2.0 support, questions
regarding Fb 2.0 should go to fb-devel.
b) I'm very surprised that Fb 1.5 accepted your original query and
thinks your query and Fb 1.5 aren't doing things correctly.

That being said, Fb 2.0 shouldn't freeze, but return an error so it
ought to be reported as an error to Fb-devel.

You have no distinction whatsoever between the table of the outer and
inner select, so I find it hard even to guess what you are trying to
do. If you are simply trying to find duplicate combinations of name
and companyname, then you should simplify your query to something like:

SELECT c."ContactID", c."Name", c."CompanyName", c."Address1",
c."Address2", c."Address3", c."Address4", c."PostCode",
c."WorkPhone", c."WorkExtension", c."MobilePhone",
FROM "Contacts" c
where exists(select * from "Contacts" c2
where c2."Name" = c."Name"
and c2."CompanyName" = c."CompanyName"
and NOT (c2."ContactID" = c."ContactID"))

(I'm assuming that "ContactID" is a primary key or similar)


--- In, Robin Davis wrote:
> Hello,
> This query works in 1.53, but not in 2 (beta). Could somebody point
> me in the right direction to resolve the problem? Every time I try
> to run it with 2 beta everything freezes up.
> Thanks in advance,
> Rob Davis
> SELECT "ContactID","Name", "CompanyName", "Address1", "Address2",
> "Address3", "Address4", "PostCode", "WorkPhone", "WorkExtension",
> "MobilePhone", "EmailName" FROM "Contacts"
> WHERE ((("Contacts"."Name")In
> (SELECT "Name" FROM "Contacts" GROUP BY "Name", "CompanyName"
> HAVING Count(*)>1 And "CompanyName"= "Contacts"."CompanyName")))
> ORDER BY "Contacts"."Name", "Contacts"."CompanyName";