Happy Business Starts Here

Re: Issue with SQL Query in Workflow

Highlighted
Valued Scholar

Issue with SQL Query in Workflow

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Valued Scholar

Re: Issue with SQL Query in Workflow

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

View solution in original post

5 REPLIES 5
Highlighted
Master

Re: Issue with SQL Query in Workflow

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

to

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

 

Highlighted
Valued Scholar

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

Highlighted
Master

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

 

 

Highlighted
Valued Scholar

Re: Issue with SQL Query in Workflow

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

View solution in original post

Highlighted
Valued Scholar

Re: Issue with SQL Query in Workflow

Thank you both for your replies!!!