Outer Join Question - "join expression not supported"

[Raven]

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]

You have the same problem than Axel, few messages up. Same solution. Actual
Jet patches do not accept that syntax but even if it would, it would not
produce the same result than MS SQL Server.


.... FROM MPDCDF RIGHT JOIN ... ON ... AND MPDCDF.QJFTID='LEN'


should be changed to

.... FROM (SELECT * FROM mpdcdf WHERE qjftid="LEN" ) AS x RIGHT JOIN ... ON
....


and remove all occurrence of MPDCDF by x, the new alias for the virtual
table.

If that does not work, make a saved query, with the subquery, and use that
saved query:

... FROM savedQuery AS x RIGHT JOIN ... ON ...



Vanderghast, Access MVP