Oracle query hangs on left join (1 Viewer)

[projecttoday]

Has anybody heard of a situation where a left or right join on Access tables linked to an Oracle database doesn't work? I have a query that hangs if I change INNER to LEFT. The LEFT version opens up fine as a saved query. But the same code when part of some vba append code hangs. Change it back to INNER and it works. The LEFT version should not be pulling up that many more records but it always hangs.

 

[MSAccessRookie]

The fact that you are linking to an Oracle Table should not be the cause of your problem. More likely it is the Query. There is not enough information here to provide you a complete answer. If you can provide the text of your Query, we might be able to do so.


If you want to test this, create a new BLANK database and IMPORT the data from Oracle instead of Linking to it. If you then take your Database and link to the test database instead of Oracle, you should be able to determine whether or not Oracle is the cause.

 

[projecttoday]

This is the query code that takes so long:

    strSQL = "DELETE FROM tblWork_OrdersTEMP"
    CurrentDb.Execute strSQL, dbFailOnError
    strSQL = ""
    strSQL = strSQL & "INSERT INTO tblWork_OrdersTEMP ( WONROV, WONUM, [WOCU#], [WOCO#], [WODV#], [WOFR#], WOTYC, WOEDT, "
    strSQL = strSQL & "WORCDT, WOSTT, [WOIA#], WOSTA, WOREA, WOPB1, WOPC1, WOPC2, WOAC2, WOCM1, WOCM2, WOCM3, WOIDT, WOQCD, "
    strSQL = strSQL & "WOPOL, WOCTG, WOOSDT, WOCLS, WOHNUM, WOCAD, WONTK, WOPTT, CUNAM, [CUHO#], HOBLD, HOSTN, HOAPT, HOCIT, "
    strSQL = strSQL & "HOSTC, HOCSL, HOZIP5 ) "
    strSQL = strSQL & "SELECT WONROV, WONUM, [WOCU#], [WOCO#], [WODV#], [WOFR#], [WOTYC], [WOEDT], [WORCDT], WOSTT, [WOIA#], "
    strSQL = strSQL & "WOSTA, WOREA, WOPB1, WOPC1, WOPC2, WOAC2, WOCM1, WOCM2, WOCM3, WOIDT, WOQCD, WOPOL, WOCTG, WOOSDT, WOCLS, "
    strSQL = strSQL & "WOHNUM, WOCAD, WONTK, WOPTT, CUNAM, [CUHO#], HOBLD, HOSTN, HOAPT, HOCIT, HOSTC, HOCSL, HOZIP5 "
    strSQL = strSQL & "FROM (WVALIVFILE_WORDMPF LEFT JOIN WVALIVFILE_CUMSTPF ON (WVALIVFILE_WORDMPF.[WOCU#]=WVALIVFILE_CUMSTPF.CUCNO) "
    strSQL = strSQL & "AND (WVALIVFILE_WORDMPF.WONROV=WVALIVFILE_CUMSTPF.CUNROV)) LEFT JOIN WVALIVFILE_HOSTPF ON "
    strSQL = strSQL & "(WVALIVFILE_CUMSTPF.[CUHO#]=WVALIVFILE_HOSTPF.HONUM) AND (WVALIVFILE_CUMSTPF.CUNROV=WVALIVFILE_HOSTPF.HONROV) "
    strSQL = strSQL & "WHERE (WOIDT Between 1120601 And 1120622 And WOSTT='CP') Or (WOUDT=1120501 And WOSTT='CN') Or "
    strSQL = strSQL & "(WOTYC In ('UP','IN','RS') And WOSTT Not In ('CP','CN'))"
    CurrentDb.Execute strSQL, dbFailOnError

When I change the 2 LEFTs to INNER it goes fast. The number of additional records for the left join is small. And when I use Access copies of the 3 tables, the left join works just fine.

I am at the point where I think I'm going to take the join out completely and just do the main table by itself and then do an update of the result with the lookup tables because I really need to get all the work order data whether the lookup records are there or not.