Problem with outer join - Invalid operation (Error 3219) !?

[Ivan]

Hi,

I use Access 2007 (12.0.6211.1000) SPq MSO (12.0.6213.1000) in the
connection with SQL Server 2000 and the tables linked using SQL ODBC driver.

One of my saved "group by" queries seeks for all the serial numbers of our
stock on the fixed day and the other simple query seeks for the transactions
of the serial numbers after this day.

If I construct a new query bounding both queries with left (outer) join on
field serial number to see the stock and the eventual later transactions,
then I get the error message: "Invalid operation". Help gives me more detail
explanation: "(Error 3219)".

If I change the type of joining on the serial number field from left outer
join to right outer join or to inner join then I have no problem to get and
see the data!

How is this possible? Did any other notice such unusual behaviour?

Ivan

 

[Michel Walsh]

It seems you may have an ambiguous outer join: Jet SQL syntax does not use
parenthesis to indicate the order of execution, as MS SQL Server does. SO,
you have two options:

make the query in MS SQL Server and use a direct query which will
short-circuit Jet

make TWO queries, in Jet, the first one with the first outer join you
want to be executed, then, in the second query, bring the first query and
add the third table to complete the query with the second outer join. Note
that NOT ALL double outer joins are ambiguous, as you found by
experimentation. Are ambiguous those that (can) produce different result,
dependant of the order of execution.


Hoping it may help,
Vanderghast, Access MVP

 

[Ivan]

Thank you for your information.

Before I felt sure that the order of nesting of queries in Jet
unambiguousnessly defines also the order of executing. Now I am a little
doubtful.

In my particular case I also found out that if I remove one of the WHERE
criteria from the first "group by" query and put this criterion in the last
problematic ("left joined") query, then error 3219 disappears.

Ivan