Access: problem with outer join - "Join Expression not Supported"

[Axel]

Hi, I am trying to narrow down an outer join, the problem is when I
add the condition (compares char field to fixed text) to the where
clause I get no results in Access, when I try to add to the outer join
I get "Join Expression not Supported". The same query runs fine in SQL
Server (but I can not run it there in the production environment
because one of the tables is on an AS400).

the condition is MPDCDF.QJFTID='LEN'
the outer joined table is MPDCDF.
I want to cover the case where there are no matches in MPDCDF, but I
do not want to loose the condition. Why does this work in SQL Server
and how can I make it work in Access? In SQL Server it returns 3
results. If I put the condition (MPDCDF.QJFTID='LEN' ) into the WHERE
clause instead I am getting NO results. Sorry for the short
identifiers all this stuff is from an AS400, so its fairly ugly (all
names are 6 letters long and there no keys defined in any table).

thanks in advance
Axel

Here is the SQL:

SELECT MPDCDF.QJFTID, MWOHED.VHRORL, MWOHED.VHMFNO, MWOHED.VHPRNO,
MWOHED.VHOROQ, MPDCDF.QJOPTN, MWOOPE.VOMAQT, MWOOPE.VOOPNO,
OOLINE.OBTEDS, MITMAS.MMGRWE, MWOOPE.VOPLGR, MWOHED.VHFACI,
MWOHED.VHMAUN, MWOHED.VHRORN, MWOOPE.VOPITI, MWOOPE.VOPLGR,
MWOOPE.VOFACI
FROM MPDCDF
RIGHT JOIN (MITMAS
INNER JOIN (OOLINE
INNER JOIN (MWOHED
INNER JOIN MWOOPE ON (MWOHED.VHCONO = MWOOPE.VOCONO) AND
(MWOHED.VHMFNO = MWOOPE.VOMFNO))
ON (OOLINE.OBORNO = MWOHED.VHRORN) AND (OOLINE.OBCONO =
MWOHED.VHCONO))
ON (MITMAS.MMCONO = MWOHED.VHCONO) AND (MITMAS.MMITNO =
MWOHED.VHPRNO))
ON (MPDCDF.QJCONO = MWOHED.VHCONO) AND (MPDCDF.QJCFIN =
MWOHED.VHCFIN)

AND MPDCDF.QJFTID='LEN' -- this creates the error in Access

WHERE (((MWOHED.VHRORL)=[obponr]*100) AND ((MWOHED.VHPRNO) Like '%')
AND ((MWOHED.VHRORN)='0167588')
AND ((MWOHED.VHCONO)=100) AND ((MWOOPE.VODEPT)='fold')
AND ((MWOOPE.VOWOST)<'90'))
ORDER BY MWOHED.VHRORL;

 

[Michel Walsh]

Indeed, even if the expression was supported, it won't necessary give the
same result than MS SQL Server returns.

You can use a query like:

SELECT *
FROM mpdcdf
WHERE qjftid="LEN"


Save it, and then


SELECT ...
FROM someTable LEFT JOIN savedQuery
ON ...


where the condition on qjftid="LEN" does not have to be repeated in this
last ON clause


Should return exactly what MS SQL Server returns.


You can try to use a virtual table instead of the saved query.



Hoping it may help,
Vanderghast, Access MVP