Left Joins with Multiple Conditions in VBA
Hello,
I am trying to run a query in Excel VBA. The query has a left join with a two conditions -- every time I run it excel crashes. If I remove the brackets enclosing the two conditions, the query fails.
The query itself is correct because if I run it directly in Oracle it works. Also I can run it thru excel via Date->Import External Data but not thru VBA.
Also if I restrict myself to one condition instead of two it works thru VBA.
Any one have any insights into what the problem could be? Or any proposed workaround... Thanks in advance.
Saleem
Dim db As Database
Set db = OpenDatabase("", dbDriverPrompt, False, "ODBC;DSN=FM;")
Query = "select VCI.isec_id, VCI.inst_class, CVL_IDENT_BB.EXT_IDENT, VCI.pl_ccy as CCY " _
& "from V_CONT_INMK VCI " _
& "left join IDENTIFIER CVL_IDENT_BB on (CVL_IDENT_BB.tdp_id = VCI.isec_id and " _
& "CVL_IDENT_BB.ident_type = " & Chr(39) & "BB_TCM" & Chr(39) & ") " _
& "where VCI.inst_class in (" & Chr(39) & "ORD" & Chr(39) & ", " & Chr(39) & "DR" & Chr(39) & ", " & Chr(39) & "CFD" & Chr(39) & ")"
Dim FM As Recordset
Set FM = db.OpenRecordset(Query, dbOpenDynaset, dbSeeChanges, dbReadOnly)
Hello,
It is the left join with multiple conditionss that is causing the problem. Specifically
"left join IDENTIFIER CVL_IDENT_BB on (CVL_IDENT_BB.tdp_id = VCI.isec_id and " _
& "CVL_IDENT_BB.ident_type = " & Chr(39) & "BB_TCM" & Chr(39) & ") "
If I just left join on one condition it works. Specically
"left join IDENTIFIER CVL_IDENT_BB on CVL_IDENT_BB.tdp_id = VCI.isec_id"
The left join with mutliple conditions query does work when tried directly via Oracle and it also works when tried thru Excel Tools Import Data. Just doesn't work thru VBA.
Thanks for your help.
Saleem
from the support engineer:
According to your problem, I want you try “"left join IDENTIFIER CVL_IDENT_BB on CVL_IDENT_BB.ident_type = " & Chr(39) & "BB_TCM" & Chr(39) & "”. It’s the second condition of your left join. Tell me your result and I will follow up it with you.
And MarcD post the question” When you import through Excel import Data do you use a specific method? like some ODBC or Oracle method ? that isn't the same when you connect to it with your DNS? Also how many rows does the query return? have you tried to make it return a very small amount with the TOP 5 or top 1 clause ?” I also want to know your answer. Thanks, that’s will help us find the root cause.
-brenda (ISV Buddy Team)
