1

Salesforce Marketing Cloud query activities 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.

For example, the query below has numerous hard-coded values that I would store in Parameters_DE

SELECT
Food,
Menu.Price,
Desserts.Name,
Vendor.Supplier
FROM
Food_DE
JOIN
Menu_DE as Menu
ON
Menu.Price > 20
JOIN
Desserts_DE AS Desserts
ON
Name = 'Cake'
JOIN
Vendor_DE as Vendor
ON
Supplier = 'McDonalds'
WHERE
(
Menu.Rating > 3
AND
Desserts.Sugar_Content < 50
AND
Supplier.Certified = 'True'
)

Is there any way to replace the multiple hard-coded values such as 20, 'Cake', 'McDonalds', 3, 50 and 'True' by referring to Parameters_DE using JOINs or other means given the limitations that SF Marketing Cloud does not support variables or temporary tables in query activities? I know it is certainly possible if there was only one hard-coded value but I am not sure how to make it work with multiple.

  • Hi CodeMonkey, is the goal of this query to produce a Result Table that contains only Menu items that exist in "Food_DE" which match the criteria specified in "Parameters_DE"? If so, how often will Parameters_DE be updated. Are you likely to add/remove parameters, or will they always be "Rating, Price, Supplier, etc.."? – Cameron Robert Mar 6 '17 at 23:26
0

You can always join to a parameter data extension:

SELECT
Food
, Menu.Price
, Desserts.Name
, Vendor.Supplier
FROM Food_DE
inner join Parameters_DE as p on queryname = 'food1'
JOIN Menu_DE as Menu ON Menu.Price > p.price
JOIN Desserts_DE AS Desserts ON Name = p.dessertName
JOIN Vendor_DE as Vendor ON Supplier = p.supplier
WHERE (
    Menu.Rating > 3
    AND Desserts.Sugar_Content < 50
    AND Supplier.Certified = 'True'
)

Updating the Parameters_DE can be done with another query:

select 
'20' as price
, 'cake' as dessertName
, 'McDonalds' as supplier
, 'food1' as queryname
/* name: Parameters_DE_Update */
/* target: Parameters_DE */
/* action: update */
| improve this answer | |

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.