This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(22.400000000000002, 71%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EK%3C/text%3E%3C/svg%3E)
Hi Experts,
I have a requirement to pass the variable value output into Sql query where clause in Copy Activity(Azure Data Factory).
Need help to assign variable value in Where clause condition - Not sure what syntax we have to follow.
Query:
SELECT columns......
FROM TABLE1 a WHERE Column1 (
Col2,
Col3,
Col4,
TO_DATE (
(SELECT TO_CHAR (EFFECTIVE_END_TIMESTAMP,
'MM/DD/YYYY:HH:MI:SSAM')
FROM TABLE2
WHERE C_NUMBER = variables('V_LKPOUTPUT')),
'MM/DD/YYYY:HH:MI:SSAM'),
TO_DATE (
(SELECT TO_CHAR (DATA_AS_OF_TIMESTAMP, 'MM/DD/YYYY:HH:MI:SSAM')
FROM TABLE2
WHERE C_NUMBER = variables('V_LKPOUTPUT')),
'MM/DD/YYYY:HH:MI:SSAM')) = 'A'
AND col5 = 'N'
and Col6 is null
and Col7 = 'Y';
Please find the attached screenshot for quick reference to see how the SQL query is, All i need is how to frame the syntax to assign variable in where clause.
Thanks, Greatly Appreciated your help.
Regards,
Kumar
Hey,
You can use
@{variables('<<variablename>>')}
like
SELECT columns......
FROM TABLE1 a WHERE Column1 (
Col2,
Col3,
Col4,
TO_DATE (
(SELECT TO_CHAR (EFFECTIVE_END_TIMESTAMP,
'MM/DD/YYYY:HH:MI:SSAM')
FROM TABLE2
WHERE C_NUMBER = @{variables('v_Lkpoutput')}),
Hi Experts,
After deep analysis i understand the issue is seems with Set Variable Output. If you observer below output, the Value is coming along with Square brackets.
{
"name": "V_LKPOUTPUT",
"value": [
"2208"
]
}
I have written below logic in SetVariables --> Value (section) --> Dynamic Content. So, from step1 # Lookup, it first fetches date value and that column name is 'C_NUMBER', and in Step#2, we are trying to assign this Step1 Output into Variable named 'V_LKPOUTPUT'.
In order to fetch only the value from the Step1 Output, Currently the logic is written by using split function like shown below: @last lion (split(activity('Lookup').output.value[0].C_NUMBER,':')).
The output in Value section is coming with [] which is causing issue when it is passing to where clause in next copy activity sql query so we need to know the logic how to eliminate the Square brackets in Value output:
So expected output should like below:
{
"name": "V_LKPOUTPUT",
"value": "2208"
}
Please share if any thoughts how to address this, Thanks for your needful.
Regards,
Kumar
@Kumar
what is the type of variable?
and can you display the output of your lookup activity
Hi @Nandan Hegde -
Type of the Variable is - Array
Ouput is:
{
"name": "V_LKPOUTPUT",
"value": [
"2208"
]
}
Screenshots also attached.
Thank You.
Regards,
Kumar
Please find the attached Lookup Output, sorry in earlier screenshot i shared SetVariable Output - Now here i'm sharing Lookup Output.
Regards,
Kumar.
Hey,
Below is the lookup output which you shared
Below is your set variable :
Can you be little clear as to what you are trying to achieve here ?
My guess is it should be @activity('Lookup').output.value.Production_Cycle_Number?
WHy are you using Split etc?
And also for you to leverage the variable in SQL , it should be of type String based on your query
Hi @NandanHegde,
As you suggested I have modified the variable type from Array to String and also changed the formula as you suggested,
activity('Lookup').output.value.PRODUCTION_CYCLE_NUMBER
But instead of result value for column 'PRODUCTION_CYCLE_NUMBER' as '2208', we are getting output as shown below with column name in the output.
{
"name": "V_LKPOUTPUT",
"value": [
"activity('Lookup').output.value.PRODUCTION_CYCLE_NUMBER'"
]
}
The intention of using split function is to get only value from JSON output.
If you observe carefully, the lookup output is:
"value": [
{
"PRODUCTION_CYCLE_NUMBER": "2208"
}
]
So I need to capture the 2208 from the above lookup output - All i wanted is this 2208 and pass this into select query where condition in Copy Activity.
Please advise how to handle this, Thank You.
Regards,
Kumar.
Please share the ADF Pipeline JSON.
Hi @Nandanhegde - Thanks for your precious time and reply.
Surprisingly the same has been working now.
The formula remains same, but the variable type changed from Array to String fixed the issue, I missed this small logic and looking out for other options - hahahah Nice lesson :) Thanks,
Formula i used to pick only value from the JSON Response from Lookup output: @last lion (split(activity('Lookup').output.value[0].PRODUCTION_CYCLE_NUMBER,':'))
Since the issue is fixed, I'm closing this question/conversation.
Highly appreciated your kindness for attempting to help and spending your precious time dear NandanHegde. Thanks,
Regards,
Kumar.
Glad I could be of help :)
Can you please accept it as answer as it might benefit other community members as well.
Thank you for using MSFT QA
The variable type changed from 'Array' to 'String' fixed the issue.
Formula i used to pick only value from the JSON Response from Lookup output: @last lion (split(activity('Lookup').output.value[0].PRODUCTION_CYCLE_NUMBER,':'))
Since the issue is fixed, I'm closing this question/conversation.
Highly appreciated your kindness for attempting to help and spending your precious time dear @NandanHegde. Thanks,
Regards,
Kumar.