Translate formula to SQL

lnguyen

Hi,

I'm working on a Redshift server and I have a simple formula to translate our campaign code to corresponding text using the first letter of the code in a Prepare recipe

However I can't use SQL engine to compute the formula.

I see that we can write a custom SQL translation but I don't know what it expects (a full select from code or just some parts?)

My formula is below, thanks a lot for your help.

And I have the same problem using the Now() function in a formula to retrieve today date?

if (substring(first_promo_code,0,1) == "A","Autopro",
if (substring(first_promo_code,0,1) == "B","Couponning dans autre revue",
if (substring(first_promo_code,0,1) == "E","Encarts",
if (substring(first_promo_code,0,1) == "F","Opérations spéciales courtes durées",
if (substring(first_promo_code,0,1) == "I","Internet",
if (substring(first_promo_code,0,1) == "M","Mailing",
if (substring(first_promo_code,0,1) == "N","Parrainage",
if (substring(first_promo_code,0,1) == "H","Délégués religieux",
if (substring(first_promo_code,0,1) == "U","Délégués jeunesse/Réseau jeunesse",
if (substring(first_promo_code,0,1) == "W","Export",
if (substring(first_promo_code,0,1) == "Z","Divers",
if (substring(first_promo_code,0,1) == "D","Divers",
if (substring(first_promo_code,0,1) == "L","Télémarketing",
if (substring(first_promo_code,0,1) == "C","Groupeurs",
if (substring(first_promo_code,0,1) == "G","Groupeurs",
if (substring(first_promo_code,0,1) == "R","Relances",
if (substring(first_promo_code,0,1) == "P","Reconduction des contrats en prélèvement",
"NR"
)))))))))))))))))

---

Operating system used: Window

Manuel

Hi,

Instead of hardcoding this mapping in a formula, you should consider creating an editable table, sync that to a dataset and then use a join. With these steps, you would move the logic to the SQL engine.

I hope this helps.