Hi I’m new to Toad using Toad Data Point 3.8.1.677 Base Edition. I have created a cross connection query using one MS Access 2010 table and three DB2 tables. When I make them all inner joins my query works fine. When I switch one join (between Access and one DB2 table) to a left join, I get an error “[Microsoft][ODBC Microsoft Access Drive] JOIN expression not supported”.
In the interest of full disclosure, the join uses a substring of the DB2 field per snippet below (real DB names masked). However this join works fine when it is an inner join, so that doesn’t appear to be the issue.
MyDB2Database.TB_DIM_PRD_PKG DPP
LEFT JOIN MyAccessDatabase.tbDIM_ExtHP_Market_Segment DIM
on RIGHT(DPP.PKG_NM,5) = DIM.BPL
Does my version of Toad support this type of join? I don’t want to waste more time tweaking syntax if it is simply not supported. Thanks for any comments 
hi BoulderRidge,
I believe MS access DB engine has specific rules in terms of inner joins combined with outer joins.
Could you please read the following solution and let me know if that helps?
https://stackoverflow.com/questions/28685674/left-join-sql-join-expression-not-supported
Martin
Hi Martin,
First thank you so much for promptly responding to my question with helpful info. Sorry I didn’t get time to post a detailed reply sooner.
I did try nesting the left join inside an inner join and it did not work. It is possible I didn’t construct the nesting correctly, as I typically don’t nest my joins and the DB2 tables cannot be joined in a linear sequence since one is a fact table and the two dimension tables must both join to the fact table.
I also tried it with the inner join criteria in the Where clause (i.e. FROM Table1, Table2, Table3, Table 4 Where Table 1.key = Table2.key etc.) but had to keep the Left join in the FROM clause and again that combination failed.
After all that, I had to be mindful of my deadline and the actual goal which was to find rows in my DB2 dataset that didn’t match my Access table. I wound up exporting the results of the DB2-only query and loading it into Access where I could do the left join easily.
Still would like to figure out how to make this type of query work (the DB2 dataset can be quite large), so if I need something similar again I will post all the variations that I tried and maybe you will see where my mistake lay. Again your help was much appreciated!
–BoulderRidge
BoulderRidge,
Thanks for the information.
Could you please write down the query structure that you try to achieve? Is it something like below? (i would like to simulate it on my side)
SELECT fact.*
FROM connectionDB2.myFactTable as fact
LEFT JOIN connectionMSAccess.myDimTable as dim on dim.colKey = fact.colKey
WHERE fact.Category = ‘abc123’
Also could you please try the example in the latest TDP release (v4.2)? Do you have a PC where you can try that or are you running this dev environment on some shared production server? I just want to ensure we do not reinvent the wheel.
Martin
We don't officially support cross-connection queries in the Base edition. They are executed by Microsoft Access engine which is very limited. We only support Cross-Execution in Pro edition and we have written our own optimizer. It support left and right outer joins. I suggest you download a trial and give it a try.
Hi Martin,
I have tried to replicate the situation with partial success. The query below is an example of the joins I need and one error I get. However this query does run if I switch the LEFT join to an INNER join, which proves the rest of the syntax is fine.
I am unable to upgrade versions or download a trial version on my corporate laptop, so I can’t try your request.
This is no longer urgent as the workaround got me the results I needed, but if there is a way to make a left join work between these two connections I would appreciate knowing how to do that.
thanks! - BoulderRIdge
SELECT
DIM.ClientCodeNbr
,DCG.GRP_NM
,YEAR(DME.MBR_EXPIR_DT) as MbrExpireYear
,COUNT(*) as MbrEligRows
FROM
Dim_Tables.tbDIM_Market DIM
LEFT JOIN
MyDB2DataSource (MyUserID).MyDB.DIM_CUS_GRP DCG
on DIM.ClientCodeNbr = RIGHT(DCG.GRP_NM,5)
,MyDB2DataSource (MyUserID).MyDB.FCT_ELIG FME
,MyDB2DataSource (MyUserID).MyDB.DIM_MBR_ELIG DME
where
DCG.GRP_SYS_ID = FME.GRP_SYS_ID
and FME.MBR_ELIG_SYS_ID = DME.MBR_ELIG_SYS_ID
and DCG.CUST_NM = ‘MYFILTERSTRING’
group by
DIM.ClientCodeNbr
,DCG.GRP_NM
,YEAR(DME.MBR_EXPIR_DT)
order by
DIM.ClientCodeNbr
,DCG.GRP_NM
,YEAR(DME.MBR_EXPIR_DT)
;
