A report developer (a non-IT business user) came to me regarding an issue in a workbook of theirs.  The reason isn't relevant because there was something else in the workbook that challenged me.  I saw that it used Custom SQL-- something I heavily frown upon! -- and with a JOIN filter that took some pondering to get right.  Please note that I have modified the SQL for brevity as it's the concepts we are concerned with.  The data source is an IBM DB2 database.

 

Here's the first part that furled my brow, highlighted in orange:

select

     V1.CO_CD

    ,V1.FG_MDL_NO

/*

    ,t4.OEM_MAKE_CD

    ,t4.OEM_MAKE_NAME

    ,t4.MAKE_MDL_GVW_RATG_CD

    ,t4.MDL_SER_CD_BY_GOVT

    ,t4.FIN_CNTLG_REF_CD

*/

    ,V1.MAKE_BUY_CD

    ,V1.COMP_GRP_NAME

    ,V1.SUPLR_CD

    ,'XXXXXXXX' as SUPLR_NAME

    ,V1.SUPLR_TYPE_CD

    ,V1.HT_CD

    ,'######' as CURR_HTS

    ,case

        when v1.ht_cd='######'

        then null

        else '•'

     END AS HTS_CHGD

    ,V1.PREF_CRITR

    ,V1.PRODCR_INDC

    ,Trim(T1.ITEM_DESC) as DESCRIPTION

    ,V2.FACILITY_ABBR

    ,T3.DEPT_CD

    ,trim(T3.BYR_CD) ||'-'|| trim(T3.BYR_NAME) as BUYER

from ...

 

Why the bulk comment (/*...*/)?  If it's not needed then don't include it as it can be added later if necessary  Get rid of it!

'XXXXXXXX' as SUPLR_NAME served no purpose, it wasn't used.  Get rid of it!

Same for '######' as CURR_HTS , get rid of it!

The CASE statement can be made into a calculated field.  Get rid of it!

TRIMming the description can be done in a calculated field or where needed.  Get rid of it!

The BUYER field can be a calculated field, too.  Get rid of it!

We need to remember that if we are not going to use Custom SQL then we need to convert it into such a form that doesn't require functions in the SELECT body.

 

Next came the FROM body:

...

from

    customs.rvc_tool_nafta_v V1

        LEFT OUTER JOIN CUSTOMS.ITEM_MASTER T1

            ON V1.CHILD_ITEM_NO=T1.ITEM_NO

            and v1.co_cd=t1.co_cd

        left outer join customs.facilities_dim_v v2

            on v1.plnt_cd=v2.plant_cd

        left outer join customs.supplier_parent t2

            on v1.suplr_cd=trim(t2.suplr_cd)

        left outer join customs.item_loc_vendor_dim_v v3

            on v1.co_cd=v3.co_cd

            and v1.org_cd=v3.org_cd

            and v1.plnt_cd=v3.plnt_cd

            and v1.suplr_cd=v3.suplr_cd

            and v1.child_item_no=v3.item_no

            and v3.prim_alt_vndr_cd='P'

        left outer join customs.purchasing_buyer t3

            on v3.ppf_byr_cd=t3.byr_cd

/*

        left outer join customs.udi_itc_mdl_xref t4

            on trim(V1.fg_mdl_no)=trim(t4.oem_mdl_cd)

*/

where

    v1.make_buy_cd='B'

 

Again with the comment!  Get rid of it!

Notice the blue line, a condition of the JOIN, and the magenta line, a condition for the entire output.

 

Getting started

What I did was add the necessary tables to the Tableau data source canvas and created the basic JOINs.  This got the information into Tableau so I could then work with the conditionals.  Here's what it looks like and this shows the ITEM_LOC_VENDOR_DIM_V table (technically a view but just roll with me on this, okay?) that will be LEFT JOINed to the RVC_TOOL_NAFTA_V table with that blue condition:

 

Taking care of the WHERE condition

Next, doing the easy part first, I created a data source Filter:

Thus the overall WHERE condition, v1.make_buy_cd='B', is now taken care of

 

Taking care of the JOIN condition, starting on the right side

Now the conditional part (blue text) for this LEFT JOIN:

     ...left outer join customs.item_loc_vendor_dim_v v3

            on v1.co_cd=v3.co_cd

            and v1.org_cd=v3.org_cd

            and v1.plnt_cd=v3.plnt_cd

            and v1.suplr_cd=v3.suplr_cd

            and v1.child_item_no=v3.item_no

            and v3.prim_alt_vndr_cd='P'

        left outer join customs.purchasing_buyer t3...

This was a real head-scratcher for me because I'm not familiar with creating 'custom' Join Clauses on Tableau's data source canvas, thus it took some experimenting before I got the correct results.  Here's the final solution:

See the last line?  Well, it turns out both the left and right side MUST be filled out otherwise the custom JOIN clause won't "stick", it will disappear.  If you don't see it then it's not there.  So, how do we get that?  It's very easy, just click the drop-down arrow in the last row of the Join box and select the option "Create Join Calculation..."

You'll be presented with the calculated field editor where you can create your Join Condition just as you would create a Calculated Field:

When done simply click the green OK button and it will be added.  You must have a Join Clause for the other side of the JOIN as well.  After BOTH sides of the Join Clause have been created you can then exit the Join box.

I used parenthesis simply to make sure the order of operations happens as I expect it to but they are not required.

 

Setting the Join Condition on the left side

Both sides of the Join Condition need an entry, a value, since there's that equal ( = ) operator between them.  Since I don't care about what's on the left side of the JOIN I just make sure it is always a Boolean TRUE.  This just fulfills the need to have something there.  If TRUE is not valid for your situation simply use (1=1).  Conversely, if you need a FALSE condition you can alternately use (1=0).

 

Now, on the other side of the equal condition is where I need to make sure only rows with the letter "P" for the [Prm Alt Vndr Cd] are pulled.  The result of this needs to be Boolean (TRUE or FALSE).  The equation for the JOIN condition is now completely set.

 

How Tableau translates it

Here is what the query looks like when I select Data >> Convert to Custom SQL.  This is only for the curious and isn't necessary to do when converting Custom SQL to the data source canvas.  I'm only focusing on the FROM body since the SELECT body is now no longer "interesting" to look at:

...

FROM "CUSTOMS"."RVC_TOOL_NAFTA_V" "RVC_TOOL_NAFTA_V"

  LEFT JOIN "CUSTOMS"."ITEM_MASTER" "ITEM_MASTER" ON

    (

     ("RVC_TOOL_NAFTA_V"."CHILD_ITEM_NO" = "ITEM_MASTER"."ITEM_NO") AND ("RVC_TOOL_NAFTA_V"."CO_CD" = "ITEM_MASTER"."CO_CD") AND ("RVC_TOOL_NAFTA_V"."ORG_CD" = "ITEM_MASTER"."ORG_CD")

    )

  LEFT JOIN "CUSTOMS"."FACILITIES_DIM_V" "FACILITIES_DIM_V" ON ("RVC_TOOL_NAFTA_V"."PLNT_CD" = "FACILITIES_DIM_V"."PLANT_CD")

  LEFT JOIN "CUSTOMS"."SUPPLIER_PARENT" "SUPPLIER_PARENT" ON ("RVC_TOOL_NAFTA_V"."SUPLR_CD" = "SUPPLIER_PARENT"."SUPLR_CD")

  LEFT JOIN

    (

     SELECT "ITEM_LOC_VENDOR_DIM_V"."CO_CD" AS "CO_CD",

      "ITEM_LOC_VENDOR_DIM_V"."ORG_CD" AS "ORG_CD",

      "ITEM_LOC_VENDOR_DIM_V"."PLNT_CD" AS "PLNT_CD",

      "ITEM_LOC_VENDOR_DIM_V"."PPF_BYR_CD" AS "PPF_BYR_CD",

      "ITEM_LOC_VENDOR_DIM_V"."ITEM_NO" AS "ITEM_NO",

      "ITEM_LOC_VENDOR_DIM_V"."SUPLR_CD" AS "SUPLR_CD",

      "ITEM_LOC_VENDOR_DIM_V"."PRIM_ALT_VNDR_CD" AS "PRIM_ALT_VNDR_CD",

      "ITEM_LOC_VENDOR_DIM_V"."CURR_TS" AS "CURR_TS",

      (CASE WHEN ("ITEM_LOC_VENDOR_DIM_V"."PRIM_ALT_VNDR_CD" = 'P') THEN 1 WHEN NOT ("ITEM_LOC_VENDOR_DIM_V"."PRIM_ALT_VNDR_CD" = 'P') THEN 0 ELSE NULL END) AS "$temp0"

     FROM "CUSTOMS"."ITEM_LOC_VENDOR_DIM_V" "ITEM_LOC_VENDOR_DIM_V"

    ) "t0" ON

    (

      ("RVC_TOOL_NAFTA_V"."CO_CD" = "t0"."CO_CD")

      AND ("RVC_TOOL_NAFTA_V"."ORG_CD" = "t0"."ORG_CD")

      AND ("RVC_TOOL_NAFTA_V"."PLNT_CD" = "t0"."PLNT_CD")

      AND ("RVC_TOOL_NAFTA_V"."SUPLR_CD" = "t0"."SUPLR_CD")

      AND ("RVC_TOOL_NAFTA_V"."CHILD_ITEM_NO" = "t0"."ITEM_NO")

      AND ((CASE WHEN (1=1) THEN 1 WHEN NOT (1=1) THEN 0 ELSE NULL END) = "t0"."$temp0")

    )

  LEFT JOIN "CUSTOMS"."PURCHASING_BUYER" "PURCHASING_BUYER" ON

    (

     ("t0"."PPF_BYR_CD" = "PURCHASING_BUYER"."BYR_CD")

     AND ("t0"."SUPLR_CD" = "PURCHASING_BUYER"."SUPLR_CD")

    )

See where $temp0 is created in the sub-SELECT body?  That is the right side of the Join Condition I created on the data source canvas.  The result set of that sub-SELECT is given the alias "t0".

Inside the ON body of the LEFT JOIN is the right side of the Join Condition I created on the data source canvas.  This is where the TRUE = [Prm Alt Vndr Cd]= 'P' Join Condition I set up on the data source canvas comes into play.  What is interesting about the translation is that Tableau converted TRUE to (1=1).

 

In closing

Even with the poor Custom SQL query, the extract created by this data source took slightly less time than using a proper Tableau data source canvas one.  I'm no expert by any means but my guess is that the use of Tableau-generated CASE statements in the JOINs doesn't help.  The fact that both sides of a Join Calculation are necessary is likely the culprit.  It would be nice to have the ability to have one-sided Join Calculations, thus more closely mimic what is written in the SQL query.