Is my cross connection left join supported? - Toad Data Point Forum - Toad Data Point - Toad World
This question is not answered

Is my cross connection left join supported?

Follow / 31 May 2017 at 4:29am

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 ;)

Replies

All Replies
  • Follow / 31 May 2017 at 9:14am

    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

  • Follow / 3 Jun 2017 at 3:48pm

    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

  • Follow / 5 Jun 2017 at 8:32am

    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

  • Follow / 7 Jun 2017 at 11:52pm

    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.

  • Follow / 17 Jun 2017 at 5:36am

    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)

    ;

    ------------------