Hi,
I am trying to use Retrieve multiple by query processor to retrieve data from multiple entities.
In DSQL query I need to pass the value to where clause dynamically. I don't see parameter section in the processor properties (like we have in DB processor.)
SELECT a.AccountID, c.clientsummaryid FROM Accounts a INNER JOIN clientsummaries c ON a.AccountID = c.client_value WHERE a.pcode = vars.pCode AND a.caseid = vars.CaseId;
Can I pass the parameters as vars.VarName to dsql query? If not how can I pass this value please ?
Thanks
tkonline19
Hi,
It seems the variable vars.Id value is not passed as proper string to Dynamics. Hence dynamics was throwing error shown in above comments.
To avoid this error, dsql was set in payload in transform message component.
%dw 2.0
output application/json
---
{
"script": "dsql:SELECT column1, column2 FROM accounts WHERE accountid = '" ++ vars.accountid ++ "'"
}
Then in "Retrieve Multiple by Query" component specify #payload.script
