Marketing Cloud - Replacing Multiple hardcoded values in JOINS with values from Parameter Table

Question

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.

Answer 1

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 */