String interpolation struggle

Question

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

@markus.bohland@hotmail.de ('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

@markus.bohland@hotmail.de ('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

@markus.bohland@hotmail.de ('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

Answer 1

Hello @Poel van der, RE (Ron) and welcome to Microsoft Q&A.
Before we get to the examples, I would like to explain the rules about string interpolation.

To indicate only a subsection of text should be evaluated as an expression, and the result rendered into a string, use:

@{my_expression_goes_here}  

The @{ marks the start of the expression, and the } marks the end of the expression. Everything outside the {curly braces} is assumed to be text.
If the {curly braces} are excluded, then it is assumed everything is part of the expression.
You can have multiple expressions embedded in your text.
@@ is used to represent a single @ in text, to differentiate from an expression.

In the below, I tested each of my examples, but I did not test your example, as I do not have the appropriate table to test against.

----------

Example 1: in a Lookup using results of a previous activity

select * from @{activity('getother').output.schem}.@{activity('getother').output.tabl}  

select 3 * @{activity('getother').output.x} as y, 2 as z;  

Your update statement didn't have a close }

----------

Example 2: in a Lookup using pipeline parameters

select * from dbo.emp where FirstName like '@{pipeline().parameters.name}'  

select ID from dbo.emp where FirstName like '@{pipeline().parameters.name}' and ID >@{pipeline().parameters.min} and ID < @{pipeline().parameters.max}  

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}'  

----------

Example 3: in a Lookup using pipeline parameters and item of For Each

select @{item()} as [pageNumber] , ID , FirstName from dbo.emp where  
 ID >= @{mul(pipeline().parameters.pageSize, item())} and  
 ID < @{mul(pipeline().parameters.pageSize,add(item(),1))}  

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}'  

Answer 2

Hi @MartinJaffer-MSFT

you explained string interpolation so well, can I ask you another question. But some times I am rather struggling with concat() as well.
I succeeded to create the query beneath, but it was more trial and error. And I also see colleagues struggling with it.
If you create a concat() like this, what are the basic rules to keep in mind?
What for instance really surprises me, is why there is a preceding and ending comma when referring to parameters or items.

''',**item().COLUMN_CODE_AMT,** '''

So hope you can give some basic rules

@markus.bohland@hotmail.de ('select year(''', pipeline().parameters.p_start_date,''') as YEAR,
''',pipeline().parameters.p_start_date,''' as PERIOD_START_DATE, ''',pipeline().parameters.p_end_date, ''' as PERIOD_END_DATE,
''', pipeline().parameters.p_product, ''' as DNB_PRD_CODE,
''',item().SUB_PRD_CODE, ''' as DNB_SUB_PRD_CODE,
''', item().CATG_CODE, ''' as DNB_CATG_CODE,
''NL'' as COUNTRY_CODE,
''',item().COLUMN_CODE_AMT, ''' as DNB_COLUMN_CODE_AMT,
CAST( GETDATE() AS Date ) as CREATION_DATE
from ',item().SUB_PRD_CODE,'
' ,item().WHERE_CODE,'')

Regards
Ron