I am using this query in my ADF pipeline:
{@if(
equals(pipeline().parameters.IsUpsert,false),pipeline().parameters.SourceSQL,
if(
equals(pipeline().parameters.daysFilterEnd, 0) ,
concat(pipeline().parameters.SourceSQL, ' WHERE ',
pipeline().parameters.SourceDateFilterColumn, ' BETWEEN ',
uriComponentToString('DATEADD%28day%2C+DATEDIFF%28day%2C+0%2C+GETDATE%28%29%29%2C-'),
string(pipeline().parameters.daysFilterStart),uriComponentToString('%29'),' AND GETDATE()'),
concat(
pipeline().parameters.SourceSQL, ' WHERE ',
pipeline().parameters.SourceDateFilterColumn, ' between ',
'DATEADD(day, DATEDIFF(day, 0, GETDATE()),-',
string(pipeline().parameters.daysFilterStart),')',
' and ',
'DATEADD(day, DATEDIFF(day, 0, GETDATE()),-',
string(sub(pipeline().parameters.daysFilterEnd,1)),')'
) )
)}.
With daysFilterStart = 3 and daysFilterEnd = 0 , I am using the correct SourceDateFilterColumn. Yet my query keeps failing, saying wrong syntax near "WHERE". I've tried various changes but can't resolve it.
The final output should run the SQL query from the control table
WHERE SourceDateFilterColumn BETWEEN DATEADD(day, DATEDIFF(day, 0, GETDATE()), -3)
AND DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0);
Example:
SELECT
[ID], [ProductReferenceID], [FrontOfPackGDAID], [FooterText],[DisplayOrder], [CreatedAt],
[DeletedAt], [DeletedBy]
FROM
[XYZCORP].[dbo].[CUSTOMERS]
WHERE
[CreatedAt] BETWEEN DATEADD(day, DATEDIFF(day, 0, GETDATE()), -3)
AND DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0);
But to my surprise in the ADF input, this is what I am seeing:
"sqlReaderQuery": " WHERE BETWEEN DATEADD(day, DATEDIFF(day, 0, GETDATE()),-3) AND GETDATE()"
