The SQL isn't how I would have written from scratch either. The reason
for its form is that the query was originally created in Access using
the GUI tools. When the SQL got imported into the application a couple
of years later I simply hit the Access SQL button to get the SQL.
Rather than risk breaking something that worked, I COPIED and PASTED
what was there, brackets and all.

The application structure is application<->DAO<->Jet front end
database<->ODBC<->Firebird to maintain compatibility with Jet databases
without creating a code maintenance nightmare. This structure means
that the query result B is simply treated as a table. I'm not sure how
I would emulate this in isql without changing the conditions of the
test, but I am a newbie to database servers...

The ODBC driver is the IB Phoenix ODBC driver. I *suspect* something
has been broken in Vista that the IB Phoenix authors have not picked up
on yet, but it's just a guess. I don't think the problem lies in DAO,
as firstly the query continues to work with a Jet database, and secondly
MS are no longer "improving" DAO.

Regards,

Robert.

Svein Erling Tysvær wrote:

> Excepting that I would normally write
>
> SELECT DISTINCT A.X, A.Y
> FROM A
> LEFT JOIN B ON A.X = B.X
> WHERE B.X Is Null
>
> (i.e. DISTINCT rather than DISTINCTROW that I didn't even know existed in Firebird and remove the redundant parenthesis)
> this should be a normal way to find records that are part of A, but not in B. There may be other ways to do this in Firebird 2.1, but it should still work.
>
> What I'm wondering about, is whether the problem is with Firebird or your ODBC client (I would be shocked if it was Firebird, but I've been shocked before). Does isql, IB_SQL or similar report an error as well? Maybe someone is able to give you some advice if you specify which ODBC client you use?
>
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Robert
> Sent: 27. mai 2008 10:31
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Query Stops Working on Vista
>
> I have an application written originally for Jet via DAO, which has been
> modified to use Firebird via ODBC. In various places a query of the form:
>
> SELECT DISTINCTROW A.X, A.Y
> FROM A LEFT JOIN B ON A.X = B.X
> WHERE (((B.X) Is Null));
>
> has been used to find the difference between to sets, A being a table,
> and B being the result from a query. This has worked for many, many
> years on everything from Win95 onwards, and continued to work when used
> with a Firebird client running on Windows XP. However, it doesn't work
> when the Firebird client is running on Vista. When the same SQL is
> used with a Jet database on Vista all is still OK. The problem appears
> to lie with the Is Null bit; it's as if the fields in the query result
> which should be NULL are not NULL. Using a different field in B to
> detect the NULL condition doesn't solve the problem.
>
> I think I've found another (superior) way of meeting the requirement,
> but I would like to understand why this SQL does not work on a Firebird
> client running on Vista, because I use Is Null in other queries (of a
> different form). Do you have any suggestions please?
>
> Regards,
>
> Robert.
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>

--
No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.524 / Virus Database: 269.24.1/1468 - Release Date: 5/26/2008 15:23