1 Reply Latest reply on Oct 5, 2016 10:33 PM by Dmitry Chirkov

    Error : Type mismatch between join fields during Cross Database Join

    Akriti Lal

      I have my data residing in tables of 2 different database mysql and progresDb. In order to retrieve the data I am doing a cross database join using Tableau 10.0 between these two databases .  This is a live production database on which several other applications are running so i can not make any changes at the database level. There are two problems that i am facing , any feedback , if you have faced something similar would be appreciated.

      Problem 1 : On My sql Data base i create Join between multiple tables and it is working fine . But the moment i do a cross database join with "Progress DB" the Join between last two tables start showing error in red saying "Type mismatch between join fields" .. Has anyone faced something similar.

      Problem2 : When I remove the last two tables of mysql that is showing error and join with the table of progressdb , the new join between mysql table and progressdb table also shows "type mismatch" . Now the question here is ,the Id column that is being used to map is having different type like in mysql it is defined as string while in progresdb it is defined as number . Can we do anything here . We can not change the type in the Database as i explained earlier ?

        • 1. Re: Error : Type mismatch between join fields during Cross Database Join
          Dmitry Chirkov

          Shameless plug: TC16 Session

           

          So here's what you do for Problem 2:

          1. Add a single MySQL table to join area - one that you are joining to Postgres
          2. Now go to Data > Convert to Custom SQL
          3. In the dialog, copy the line that selects join key and duplicate it
          4. Cast the copy: CAST(`MyColumn` as UNSIGNED) as `Proper Join Key`
          5. Now use that newly created column for the join

           

          Problem 2 is very similar - you have a join on mismatched data types but for single connection datasource we allow you to do that and let database either succeed (by doing internal casts or type normalization) or fail.

          With cross-database join we need to be more strict so you have to resolve that conflict manually - Custom SQL is most convenient way but I'd reevaluate your schema (i.e. storing numbers as strings is time and space consuming).

           

          Could you try this out and let me know how it goes?

          1 of 1 people found this helpful