Buidling SQLqueries in dynamic content ADF

Question

All

building SQL query in dynamic content / expression builder, I still find it hard.
Maybe someone can help.

This is in the dynamic content of a query in a Lookup activity, reading a SQLServer table

@markus.bohland@hotmail.de ('select * from dnb.selectie where prd_code = ', pipeline().parameters.p_product, ' and strt_dt <= ', activity('lk_hlp_report_period').output.firstRow.STRT_DT, ' and end_dt >= ', activity('lk_hlp_report_period').output.firstRow.END_DT)

prd-code = Varchar 10 defined in SQLServer and in the pipeline parameter as String. Value parameter 0010.

start-date and end-date are both defined as Date in SQLServer. They ares compared with the output of another Lookup (lk_hlp_report_period) that reads a table where dates are also stored as Date in SQLServer.

When I run the pipeline, this is the outcome

Input
{
"source": {
"type": "AzureSqlSource",
"sqlReaderQuery": "select * from dnb.selectie where prd_code = 0010 and strt_dt <= 2020-10-01T00:00:00 and end_dt >= 2021-04-01T00:00:00",
"queryTimeout": "02:00:00",
"partitionOption": "None"
},
"dataset": {
"referenceName": "ds_generic_ifp_modelled",
"type": "DatasetReference",
"parameters": {
"p_modelled_schema": "DML",
"p_modelled_table": "DNB_SELECTIE"
}
}
}

And this the error

Error details
Error code
2100
Troubleshooting guide
Failure type
User configuration issue
Details
Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near '00'. The label 'T00' has already been declared. Label names must be unique within a query batch or stored procedure.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near '00'. The label 'T00' has already been declared. Label names must be unique within a query batch or stored procedure.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near '00'.,},{Class=15,Number=132,State=1,Message=The label 'T00' has already been declared. Label names must be unique within a query batch or stored procedure.,},],'
Source
Pipeline
pl_dnb_process

What am I doing wrong?
How do I fix that 'where prod_code = 0010' becomes 'where prod_code = '0010'' ?? Since it is a varchar
How do I delete the time part of those data fields coming from the other Lookup, like 2020-10-01T00:00:00 ??

Hope someone can help and maybe point to some side / url / video were this is explained?

Regards
Ron

Answer 1

Hello @Poel van der, RE (Ron) and welcome to Microsoft Q&A.

If I understand correctly, you need to go from:

select * from dnb.selectie where prd_code = 0010 and strt_dt <= 2020-10-01T00:00:00 and end_dt >= 2021-04-01T00:00:00  

to:

select * from dnb.selectie where prd_code = "0010" and strt_dt <= "2020-10-01T00:00:00" and end_dt >= "2021-04-01T00:00:00"  

To implement double quotes we can change like below:

@concat('select * from dnb.selectie where prd_code = "', pipeline().parameters.p_product, '" and strt_dt <= "', activity('lk_hlp_report_period').output.firstRow.STRT_DT, '" and end_dt >= "', activity('lk_hlp_report_period').output.firstRow.END_DT, '"')  

Single quotes are trickier because we are already using them to demarkate the string literals. Below is an example of how to add in single quotes. Note that '''' -> ' (4x') , but when used as part of a larger string fewer are needed. This is because '' -> '.

@concat('f','''','d','a''f','''q','x''')  ->  "f'da'f'qx'"  

Answer 2

Hi @MartinJaffer-MSFT

I hope I may ask you another question also concerning building dynamic sql queries.
I have a steering/configuration table in SQLServer containing 5 columns, ‘tablename’ up until ‘where’.

I do a Lookup activity on this steering table and based on the output I have to create a SQLquery in dynamic content.
Based on the formed SQLquery I read another table.
The column Result tells how that query should look like in the end.
As you can see the columns ‘col-amt’ and ‘join’ can be NULL.
How would the @markus.bohland@hotmail.de () statement look like in the dynamic content??

Regards
Ron