Subject Re: [firebird-support] Why does this query work with 1.53 but not 2 (Beta)?
Author Ivan Cruz
Robin Davis wrote:

>This query works in 1.53, but not in 2 (beta).
>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";
Just a guess here. I believe it's related do the somewhat ambigous use of
"Contacts" as an alias in the inner select. Try:

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" C
WHERE ( ( ( C."Name" ) In ( SELECT C2."Name" FROM "Contacts" C2
GROUP BY C2."Name", C2."CompanyName"
HAVING Count(C2.*)>1 And
C2."CompanyName"= C."CompanyName" ) ) )
ORDER BY C."Name", C."CompanyName";

The version 2 is said to be more restrictive and standards adderent
with respect to aliases.