question

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

Buidling SQLqueries in dynamic content ADF

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

@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)

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

azure-data-factory
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.

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

Hello @PoelvanderRERon-6976 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'"
· 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.

MartinJaffer-MSFT avatar image MartinJaffer-MSFT PoelvanderRERon-6976 ·

Thank you for letting us know. Have a great day!

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

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’.

82627-knipsel.jpg


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 @concat() statement look like in the dynamic content??

Regards
Ron


knipsel.jpg (87.0 KiB)
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.