If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Outer Join Question - "join expression not supported"
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; |
#2
|
|||
|
|||
Outer Join Question - "join expression not supported"
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 "Raven" wrote in message ... 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; |
Thread Tools | |
Display Modes | |
|
|