question

PoelvanderRERon-6976 avatar image
0 Votes"
PoelvanderRERon-6976 asked PoelvanderRERon-6976 answered

String interpolation struggle

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.

88833-error.jpg


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


azure-data-factory
error.jpg (29.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@PoelvanderRERon-6976 did my examples help? If they solved your problem, please mark as accepted answer, otherwise let me know how I may better assist you.

0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered MartinJaffer-MSFT commented

Hello @PoelvanderRERon-6976 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;

89226-image.png

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












image.png (239.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI @MartinJaffer-MSFT

sorry for the late reaction, but really a great explanation which helped me a lot. It makes life easier instead of using concat()
Thnx again

regards
Ron

0 Votes 0 ·

Thank you for the feedback, have a great day!

0 Votes 0 ·
PoelvanderRERon-6976 avatar image
0 Votes"
PoelvanderRERon-6976 answered

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

@concat('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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.