Tableau ERROR: column reference “datasource” is ambiguous; Error while executing the query Unable to create extract

Question

I am trying to create a tde file from a live data source. I am connecting to multiple materialized views in postgres so the data source is a custom sql query. Everything in the workbook runs fine while live but when I try to extract the data, I receive the error:

ERROR: column reference "datasource" is ambiguous; Error while executing the query Unable to create extract

I do have multiple tables with the same field name so I aliased each of the fields accordingly in my custom query. It seems that when Tableau creates their query for extract, the aliasing isn't recognized. Any help is very appreciated.

SELECT
 i.trx_line_id
,i.datasource
,ie.category_type
,ss.trx_line_id
,ss.datasource
,pl.pl_cd


FROM   invoice i

LEFT   JOIN sales_structure ss ON i.trx_line_id = ss.trx_line_id
LEFT   JOIN invoice_ext ie ON i.trx_line_id = ie.trx_line_id
LEFT   JOIN product_level pl ON i.pl_cd = pl.pl_cd

WHERE  ss.sales_team_rpt IN ('a','b')

Answer 1

You are returning to Tableau a set of data where fields (datasource and trx_line_id) have the same name. A simple fix would be to alias those fields:

SELECT
    i.trx_line_id AS invoice_line_id, -- Aliased
    i.datasource AS invoice_datasource, -- Aliased
    ie.category_type,
    ss.trx_line_id AS sales_structure_line_id, -- Aliased
    ss.datasource AS sales_structure_datasource, -- Aliased
    pl.pl_cd
FROM
    invoice i
LEFT JOIN
    sales_structure ss ON i.trx_line_id = ss.trx_line_id
LEFT JOIN
    invoice_ext ie ON i.trx_line_id = ie.trx_line_id
LEFT JOIN
    product_level pl ON i.pl_cd = pl.pl_cd
WHERE
    ss.sales_team_rpt IN ('a','b');