The issue here is that because Table1 and Table2 contains CompanyID as a column in them and since you have used 'SELECT * ', Tableau is being presented two columns called CompanyID. This would not be acceptable and hence the error. I suggest you handpick the columns that you would like to use on Tableau as opposed to using ' * '.
Would that help?
Thanks for helping out. The problem is that I am using this view on a lot of different dashboards.
By simply using a SELECT *, any changes I made to the View will be automatically applied to all the data sources using that connection.
However, if I am writing this statement in full, I would need to update the custom SQL code in every single one of them every time I add a field.
The code works perfectly in SQL Server, shouldn't it be the same in Tableau as well?
I'm not 100% sure, but I tend to think it was working in the past.
I think it is one for the Product Development team then....
If you run the SQL in SQL Management Studio, you will see that it returns CompanyID twice - but then you could repeat the occurrence of a column any number of times on SQL. As Tableau uses the column names as the field names in the tableau data source, the only way I could think of getting this working was to either restrict the number of occurrences or have an alias for each repeat occurrence.
However, if you do not have a need to write Custom SQL, except for having to join multiple tables, I would suggest using the 'Multiple Tables' option on the connection screen. In this approach, Tableau provides an alias for duplicate fields. Would that help?
Thank you Siraj.
I actually end up using the method you recommend (using "Multiple Tables").
It solves the majority of my needs (except for a couple of connections where I need to add a WHERE clause to my Custom SQL).
Would be nice if Tableau could automatically rename the duplicate columns though.
Thank you again for helping out!