CODEWITHSUNDEEP
azure-data-factory
Patterson
Patterson

Reputation: 2823

Azure Data Factory Error: Objects In From clause have the same exposed names: Use correlation names to distinguish them

My copy activity is attempting query on two tables in a database called OSCAR. One of the tables 'toMasterPolicy' in the database uses the schema dlt, whereas the table with the same 'toMasterPolicy' uses the schema dbo.

When I execute the following query below, I get the error:

enter image description here

Clearly the problem is that I'm using the same name for each table. In a previous question I asked if I could add suffix to a 'TableName' to change the name of the table. It was suggested I could do the following:

@concat(pipeline().parameters.TableName,'V2')

Can someone take a look at my expression and either help me modify the expression to add a suffix as suggested i.e. @concat(pipeline().parameters.TableName,'V2')

Or show me how to use as correlation name to distinguish the tables 'toMasterPolicy'

My expression is as follows:

@

concat('SELECT ',pipeline().parameters.TableName,'.* FROM ',pipeline().parameters.tempdb,'.',pipeline().parameters.deltaschema,'.',pipeline().parameters.TableName,' LEFT OUTER JOIN ',pipeline().parameters.tempdb,'.',pipeline().parameters.Domain,'.',pipeline().parameters.TableName,' ON ',pipeline().parameters.TableName,'.signature = ',pipeline().parameters.TableName,'.signature WHERE ',pipeline().parameters.TableName,'.signature IS NULL')

Upvotes: 0

Views: 31

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8402

The objects "OSCAR.dbo.toMasterPolicy" and "OSCAR.dlt.toMasterPolicy" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

The above error you are getting is the table name you are using the above query has same table to resolve this issue you need to use aliases for each reference to toMasterPolicy Your query should look as below:

SELECT sampletable.* FROM sampletempdb.dlt.sampletable as dlttb LEFT OUTER JOIN sampletempdb.dbo.sampletable as dbotb ON dlttb.signature = dbotb.signature WHERE dlttb.signature IS NULL"

As per above your dynamic expression will look like below:

@concat('SELECT ',pipeline().parameters.TableName,'.* FROM ',pipeline().parameters.tempdb,'.',pipeline().parameters.deltaschema,'.',pipeline().parameters.TableName,'as dlttb LEFT OUTER JOIN ',pipeline().parameters.tempdb,'.',pipeline().parameters.Domain,'.',pipeline().parameters.TableName,' as dbotb ON dlttb.signature = dbotb.signature WHERE dlttb.signature IS NULL')

Upvotes: 0

Related Questions