Hi
Multiple times I have to write SQL queries in the dynamic content of a Lookup activity.
And to be honest I find it hard.
But writing a concat is also hard. Takes some time to find out if everything is in place in the concat-statement.
Looking at string interpolation, it seems to make life easier, queries clearer. So I hope someone can help me in my learning curve.
Beneath some examples.
Maybe someone can give/explain the ‘string interpolation’ translations, so next time I can do myself.
Example 1: in a Lookup using results of a previous activity
As a concat
@concat('update ',activity('lp_config_table').output.firstRow.SRC_SCM_CODE,' .',activity('lp_config_table').output.firstRow.SRC_DSET_TECH_NM,' set MS_F = ''Y'' where TBL_CODE = ''',item().TBL_CODE,''' and SQL_LN = ''',item().SQL_LN,''';select 0 as return_value;')
This is the result of the concat, which is fine.
"sqlReaderQuery": "update OPR .HLP_CHK_DUM_ROW set MS_F = 'Y' where TBL_CODE = 'DIM_SVG_PD_CRN' and SQL_LN = 'SVG_PD_ID = -99';select 0 as return_value;"
But trying to re-write it as string interpolation (forget the select 0, just there because Lookup has to return something)
"UPDATE @{activity('lp_config_table').output.firstRow.SRC_SCM_CODE}.@{activity('lp_config_table').output.firstRow.SRC_DSET_TECH_NM} SET MS_F = 'N'"
Gives an error.

Example 2: in a Lookup using pipeline parameters
@concat('select * from DML.dnb_selectie where prd_code = ''', pipeline().parameters.p_product, ''' and strt_dt <= ''', pipeline().parameters.p_start_date, ''' and end_dt >= ''', pipeline().parameters.p_end_date, '''')
How do I write this in string interpolation??
Example 3: in a Lookup using pipeline parameters and item of For Each
@concat('select year(''', pipeline().parameters.p_start_date,''') as YEAR,
''',pipeline().parameters.p_start_date,''' as PERIOD_START_DATE,
''',item().SUB_PRD_CODE, ''' as DNB_SUB_PRD_CODE,
''NL'' as COUNTRY_CODE,
sum(',item().COLUMN_NAME_NBR,') as VALUE_NBR,
from ',item().SUB_PRD_CODE,'’)
How do I write this in string interpolation ( I deleted ‘group by’ for simplicity)??
So the main question is, how do I refer in string interpolation to an outcome of a previous activity, to a pipeline parameter or to an item of a For Each activity? And then how do i write that select statement?
Regards
Ron
