Issue with SQL Query in Workflow

[vkannan]

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', 

I am passing the Subscription Id as the input to the callout.

Any pointers?

Thanks,

Vimal

[gauri]

change this line - WHERE Subscription.Name = {{Data.Subscription.Id}}

to

WHERE Subscription.Name = '{{Data.Subscription.Id}}'

[vkannan]

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

[gauri]

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 %}

[arindamkesh · Accepted solution]

If you are using a Data Query (Link) activity to run the SQL query, you can Iterate the result set using Data.LinkData.<column>

[vkannan]

Thank you both for your replies!!!