0

I'm trying to setup a COALESCE SQL function in MC where it skips empty/non-populated Mktg Tracking ID's in a DE row until it gets to a non-null one. So far I have it working in attributes that are not var-char text, but for the fields that are var-char text, instead of moving to the next populated attribute it displays the null field (in this case an empty field). I also tried populating the attibute field in the DE with 'NULL' but it just outputs 'NULL' instead of moving to the next populated attribute. Hopefully what I'm describing makes sense but wondering what I can do to get the text fields working properly? SQL query is:

Select

Coalesce(Reward_marketing_tracking_id_1, Reward_marketing_tracking_id_2, Reward_marketing_tracking_id_3) as Reward_marketing_tracking_id,
Coalesce(Reward_promo_code_1, Reward_promo_code_2, Reward_promo_code_3) as Promo_Code,
Coalesce(Reward_dollar_amt_1, Reward_dollar_amt_2, Reward_dollar_amt_3) as Reward_dollar_amt,
Coalesce(Alpha_trigger_1, Alpha_trigger_2, Alpha_trigger_3) as Alpha_Trigger,
Coalesce(Sui_code_1, Sui_code_2, Sui_code_3) as Sui_code,
Coalesce(Expiration_date_1, Expiration_date_2, Expiration_date_3) as Expiration_date,
Coalesce(Offer_start_date_1, Offer_start_date_2, Offer_start_date_3) as Offer_start_date

From
[FD-last-minute20220822_Test4]

1 Answer 1

2

So empty and null are two different things. If the field contains an empty string, this will not be read as NULL by Coalesce and it will display that value instead of moving to the next one.

To get around this you have a few options, but the one I find the best/easiest to read is to use a NULLIF() around the field. This way you can actually force the empty value to instead be null.

Something like:

SELECT
  COALESCE(NULLIF(myfield1,''),NULLIF(myfield2,''),NULLIF(myfield3,'')) as myfield
FROM [myDE]
3
  • Thanks Gortonington. So when I used that code I got an error message. 'Error saving the Query field. 'Coalaesce' is not a recognized built-in function name'.
    – Chris-L
    Sep 13, 2022 at 17:28
  • @Chris-L you misspelled Coalesce. You spelled it Coalaesce which is why it tossed the error Sep 13, 2022 at 20:02
  • Yep, I did. I figured that out earlier today when I was working on it. Your logic worked though, so thank you so much!
    – Chris-L
    Sep 14, 2022 at 21:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .