0

I have this task to build a new query activity for subscribers who didn't open or clicked in any of the newsletters our company have been sent. The following SQL query is what I've written down, and keeps me showing the same error, even though I already changed it. I switched s.subscriberKey for ID_MKT_CLOUD__c and keeps me showing an error message.

This is what i have:

Select distinct   
s.subscriberKey,  
convert(char(19),s.EventDate,20) as SendDate  
from [_sent] s  
left join [_open] o  
on s.ListID = o.ListID 
and s.BatchID = o.BatchID 
and s.SubscriberID = o.SubscriberID 
and o.IsUnique = 1  
left join [_click] c  
on s.ListID = c.ListID 
and s.BatchID = c.BatchID 
and s.SubscriberID = c.SubscriberID 
and c.IsUnique = 1  
where  
(o.SubscriberID is NULL and c.SubscriberID is NULL)

ERROR: (Error saving the Query field.Field 'ID_MKT_CLOUD__c' is required for the Target Data Extension. Ensure this field is specified in your query text.)

Can you help me please? Thank you

3
  • so the field ID_MKT_CLOUD__c is required in your target DE but doesn't exist in this query. so you could modify your s.subscriberkey as [ID_MKT_CLOUD__c]
    – EazyE
    Commented Sep 28, 2021 at 14:42
  • Welcome to Salesforce Stack Exchange (SFSE). Be sure to take the Tour & look in the Help Center. Please edit your question to explain what you mean by "I switched s.subscriberKey for ID_MKT_CLOUD__c".
    – Moonpie
    Commented Sep 28, 2021 at 14:52
  • @EazyE i did the change s.subscriberkey to [ID_MKT_CLOUD__c] (either with and without the parenthesis), and i still get an error message. This is how i have right now, gyazo.com/3dbf4409db6d5f6dc05f81c7624ff746
    – Emma Jones
    Commented Sep 28, 2021 at 15:38

1 Answer 1

1

as per your last comment / screenshot -

EazyE's original reply is correct, but your screenshot shows a misinterpretation of what was suggested. I'll try and elaborate.

ID_MKT_Cloud__c apparently is a field in your target DE.

The error tells you that in your target DE, it is in fact a mandatory field, so nothing can write data into the target without populating this field. If a query does not contain data for this field, it will throw an error.

So the problem is the mismatch between the format of source data and target DE.

In the data views (_open and _click) where you source your data, the field of interest is called subscriberkey.

So the only thing that needs to be done is:

You take subscriberkey from your source data (as you already do), you then rename subscriberKey to ID_MKT_Cloud__c using AS syntax, and it will match the format your target DE expects:

s.subscriberKey AS ID_MKT_CLOUD__c

You only do this in select statement, which is were you define the look the data should have for the target DE. This is line 2 in your code, nothing else needs changing from the original code you posted.

The JOINs underneath interact with the source data, but as the problem is the format in the target DE, no need (or point) to change any field names there.

1
  • It did work. Thank you for the full explanation.
    – Emma Jones
    Commented Sep 29, 2021 at 17:37

You must log in to answer this question.

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