azure-data-factory

Parsing a SQL query for Lookup activity

I am trying to feed the SQL query shown here into a lookup activity to get the output as below.

SQL query:

select STRING_AGG(concat('''',country,''''),',') as Col 
from abc.country 
where QID = (select QID from abc.masterCountry where countryname = 'USA')

Expected output:

'India','China',USA','UK'

I am passing the query into lookup, but I'm not getting the expected output.

Lookup query:

@replace('select STRING_AGG(country,'','') as Col 
          from abc.country 
          where QID = (select TID from abc.masterCountry 
                       where country = ''USA'')', 'country,'',''', 'concat('''',country,''''),'',''')

I also tried:

@concat('select STRING_AGG(concat('''', country, ''''), '','') as Col  
         from abc.country 
         where QID = (select QID from abc.masterCountry 
                      where country = ''USA'')')

In adf, '''' are considered as '' and hence I am not getting the desired result.

Solution

  • You need to double the quotes another time so they'll be treated literally in the call to @concat()

    @concat('select STRING_AGG(concat('''''''', country, ''''''''), '','') as Col  
         from abc.country 
         where QID = (select QID from abc.masterCountry 
                      where country = ''USA'')')