Azure data factory @concat usage..please help

Question

HI, i am trying to call multiple tables from one database such that it should fetch only current week data to sink and i am trying to use where condition in source copy activity where i am defining below query...

NOTE: item().source is used to get table name dynamiclay from list defined in lookup activity and below query throws me error ...please suggest me how to implement this logic

@markus.bohland@hotmail.de ('select * from ', item().source ,' (gjm_cutoffs gcut where gcon.cutoff_id = gcut.cutoff_id
and gcut.cutoff_id >= (select max(cutoff_id) from gjm_cutoffs where cutoff_type = 'W') ')

Answer 1

Hi @Vanipriya K ,

You should frame your query something below and it should help resolve your issue. In where condition you have to enclose the string in two single quotes shown below.

@concat('select E.EMPLOYEEID, E.LASTNAME, E.FIRSTNAME, ES.Salary , E.LOCATION from ', item().TableName,' E LEFT JOIN EmployeeSalary ES ON E.EMPLOYEEID = ES.EMPLOYEEID where E.LASTNAME = ''John1'' ')  

I believe your query should look like below.

@concat('select * from ',  item().TableName,' gcon  
JOIN gjm_cutoffs gcut  
ON gcon.cutoff_id = gcut.cutoff_id  
and gcut.cutoff_id = (select max(cutoff_id) from gjm_cutoffs gcut where cutoff_type = ''W'' ')  

Hope this helps. Do let us know how it goes.

----------

• Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Hi @Vanipriya K ,

Thanks for using Microsoft Q&A forum and posting your query.

Looks like you query formation is incorrect. from the sample you shared I see that you have used the item().source and also you have used the direct table names like gjm_cutoffs. You have used the table name from item() as well as hard coded the table names which will form incorrect query and fails the activity. You may have to reframe your dynamic expression to form actual query.

Current query you used :

I also believe you are doing join but I don't see JOIN statement in your query. Please make sure to form a correct query to run the pipeline successful.

Sample dynamic query - @concat('Select * from ', item().TableName, ' where EMPLOYEEID >=2 AND EMPLOYEEID < 4')

In case if you are still blocked, please do share the actual query (complete query) which you would want to parameterize or form using dynamic expression. I can take a look.

Hope this info helps. Do let us know how it goes.

----------

• Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators