2

Salesforce Marketing Cloud queries do not allow variables or temporary tables according to the "SQL Support" section of this official documentation (http://help.marketingcloud.com/en/documentation/exacttarget/interactions/activities/query_activity/)

I have a data extension called Parameters_DE with fields Name and Value that stores constant values. I need to refer to this DE in queries.

Using transact-SQL, an example is:

Declare @number INT
SET @number = (SELECT Value FROM Parameters_DE WHERE Name='LIMIT')
SELECT * FROM Items_DE
WHERE Price < @number

How can the above be done without variables or temporary tables so that I can refer to the value of the 'LIMIT' variable that is stored in Parameters_DE and so that the query will work in Marketing Cloud?

Index Hacker
  • 2,004
  • 3
  • 17
  • 20
  • Value can return zero or more so INT is a bad type – paparazzo Feb 27 '17 at 16:30
  • 2
    You'll get a lot more eyes on your SFMC questions over at [salesforce.stackexchange.com](http://salesforce.stackexchange.com), specifically with the [marketing-cloud](http://salesforce.stackexchange.com/questions/tagged/marketing-cloud) and [query](http://salesforce.stackexchange.com/questions/tagged/query) tags. – Adam Spriggs Feb 27 '17 at 18:42
  • @AdamSpriggs Thanks for the tip, I'll move any future questions to there. – Index Hacker Feb 27 '17 at 19:35

3 Answers3

3

This is what I would have done anyway, even if variables are allowed:

SELECT i.* 
FROM Items_DE i
INNER JOIN Parameters_DE p ON p.Name = 'LIMIT'
WHERE i.Price < p.Value

Wanting to a use a variable is indicative of still thinking procedural, instead of set-based. Note that, if you need to, you can join to the Parameters_DE table more than once (give a difference alias each time) to use the values of different parameters at different parts in a query.

You can also make things more efficient for this type of query by having a parameters table with one row, and a column for each value you need. Then you can JOIN to the table one time with a 1=1 condition and look at just the columns you need. Of course, this idea has limitations, too.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
0

You could just use the SELECT which retrieves the number in your WHERE clause:

SELECT * FROM Items_DE
WHERE Price < (SELECT Value FROM Parameters_DE WHERE Name='LIMIT')
Bill Tür stands with Ukraine
  • 3,425
  • 30
  • 38
  • 48
0

This can be done with a join 

SELECT i.* 
FROM Items_DE i
INNER JOIN Parameters_DE p 
 ON p.Name = 'LIMIT'
AND p.Price > i.Value
paparazzo
  • 44,497
  • 23
  • 105
  • 176