- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Hi,
I am trying to create a workflow where we send an email to a specific address with some additional details of the subscription.
The workflow is triggered from the subscription creation event and i am having difficulties compiling this SQL query.
The following
SELECT account.name, account.id, productrateplancharge.name
FROM Account JOIN Subscription ON Account.id = subscription.accountid
JOIN RatePlan ON Subscription.id = rateplan.subscriptionid
JOIN ProductRatePlan ON RatePlan.productrateplanid = ProductRatePlan.id
JOIN ProductRatePlanCharge ON ProductRatePlan.id = ProductRatePlanCharge.productrateplanid
WHERE Subscription.Name = {{Data.Subscription.Id}}
I get the following error,
Invalid Query. Query failed (#20200218_121437_07897_njvbv): line 6:23: '=' cannot be applied to varchar, boolean
OR
Invalid Query. line 6:25: mismatched input '<EOF>'. Expecting: 'ALL', 'ANY', 'SOME',
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
If you are using a Data Query (Link) activity to run the SQL query, you can Iterate the result set using Data.LinkData.<column>
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Re: Issue with SQL Query in Workflow
change this line - WHERE Subscription.Name = {{Data.Subscription.Id}}
to
WHERE Subscription.Name = '{{Data.Subscription.Id}}'
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Re: Issue with SQL Query in Workflow
Thanks Gauri, that worked.
I have a follow up to this question, since i am only retrieving 1 row from the data source, how do i use the columns retrieved downstream in the workflow.
My requirement is to put the columns in an email notification.
Here's my query again,
SELECT account.name AS AccountName, account.id AS AccountId, rateplancharge.name AS ChargeName, rateplancharge.quantity AS Quantity,
Subscription.trialid__c AS AdminEmail,Subscription.initialterm AS Term
FROM Account JOIN Subscription ON Account.id = subscription.accountid
JOIN RatePlan ON Subscription.id = rateplan.subscriptionid
JOIN RatePlanCharge ON RatePlan.id = rateplancharge.rateplanid
JOIN ProductRatePlan ON RatePlan.productrateplanid = ProductRatePlan.id
JOIN ProductRatePlanCharge ON ProductRatePlan.id = ProductRatePlanCharge.productrateplanid
WHERE Subscription.id = '{{Data.Subscription.Id}}'
Thanks,
Vimal
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Re: Issue with SQL Query in Workflow
yes you will be able to access the columns in the downstream Email task.
Inside the "Body" you would be able to iterate through the Data using liquid syntax.
for example -
{% for acc in Data.Account %}
Account Name is {{ acc.AccountName }}
Account Id is {{ acc.AccountId }}
{% endfor %}
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
If you are using a Data Query (Link) activity to run the SQL query, you can Iterate the result set using Data.LinkData.<column>
- Mark as New
- Bookmark
- Subscribe
- Permalink
- Email to a Friend
- Report Inappropriate Content
Re: Issue with SQL Query in Workflow
Thank you both for your replies!!!