I am trying to write a SQL query in automation studio but one column in where clause should be variable or configurable. Means, we should not hard code the value for a particular column and we should not update the automation everytime if there is a change in value. Below is the query in which where clause column value should come from some configurable source in marketing cloud.

Select name, age 
from employee
where age = '34'

SFMC does not allow you to use SQL variables. Your best bet is to create another DE with the value you want in it. E.g. create a DE that only has a field of 'Age' and then inside of that DE, your only row is 34.

You then adjust your query to join records based only on the fields matching.

Select e.name, e.age 
from employee e
inner join variableDE v
on e.age = v.age

Then any changes you need made to the age conditional you can just update the variableDE with the new age and the query will pull based on that.

  • 1
    great solution. i'll leave my answer in, but this is miles ahead. – Jonas Lamberty Jun 3 '19 at 12:49
  • @Gortonington : Thankyou!! It works.. :) – R Suri Jun 3 '19 at 14:07

To my knowledge, the only way to do this is to update the query Text as a whole through SSJS, which means updating the SQL Activity (which I understand you want to avoid - however, there is no "lookup" or similar in an SQL Query in marketing cloud).

I can thus only give you a workaround.

Your SQL Query would have to turn into a two-step automation.

1) SSJS to write the actual query Text, where the variable part of the query could be looked up in e.g. a data extension, and the query for step 2) created.

2) the SQL Query.

Step 1 would look something like this:

<script type="javascript" runat="server">
 var dynamicAge = "" //this can be looked up or otherwise dynamically created
 var existingQuery_Key = "xxxxxxx"
 var exportQueryText = "SELECT name, age from employee where age = '" + dynamicAge + "'"
 var qd = QueryDefinition.Init(existingQuery_Key);
 var update_status = qd.Update({QueryText: exportQueryText});

Step 2 initially could be just a generic, syntactically valid query like "select * from _subscribers", because it would be dynamically updated on each subsequent run.

Note that this entire automation will not be able to be scheduled, because it has to update itself, and once it's turned off for the update, it's turned off :)

So my approach would be: Leave the automation unscheduled, and run a second, scheduled automation which only contains an SSJS Block like this one: https://sprignaturemoves.com/retrieving-and-starting-an-automation-with-ssjs/

This automation acts as the scheduler and basically presses "run once" for your automation at a defined time.

Clumsy? Yes, it looks clumsy to have 3 activities in two automations do the job of one dynamic query - but it works (at the cost of one additional automation run). If someone else has a solution for an actually dynamic SQL, forget about this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.