Translate formula to SQL

lnguyen
Level 2
Translate formula to SQL

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

0 Kudos
1 Reply
Manuel
Dataiker Alumni

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.

0 Kudos