3

I'm trying to run a query that contains a few joins. It is populating a data extension. However, when I "Run Once", not only does it fill the extension with data, but there is no record in the activity log that it ran successfully or there was an error.

I'm new to MC, It's possible I'm missing something. It's also possible that my query below is not correct. It also seems like I don't have a rock s=olid way of troubleshooting any issues. Is there logging I can turn on somewhere to help?

SELECT o.Id as OutcomeId,
   c.FirstName as FirstName,
   c.LastName as LastName,
   c.Email as Email,
   c.Id as Id
FROM Outcome__c_Salesforce AS o
INNER JOIN LOCATIONCONTACT__C_SALESFORCE AS l 
   ON l.Location__c = o.Location__c
INNER JOIN Contact_Salesforce AS c 
   ON c.Id = l.Contact__c
WHERE o.CreatedDate < DATEADD(hh, 6, getdate()) 
   AND o.CreatedDate > DATEADD(hh, 4, getdate()) 
   AND o.RecordTypeId = '0120W000001YbytQAC'
2

I would recommend creating an automation and running your query inside the automation. By doing it this way, you'll be able to see when the automation/query was started and when it finished. You can also see if it completed without errors. If it did error, you can see some limited descriptions of the errors on the "Activity" tab.

| improve this answer | |
1

As jake314159 suggested, you can run it in an Automation to see a limited description of the error if it fails, or try running it in Query Studio, which should also display some sort of error message if you have any mistakes. On top of that, if you use Query Studio, you will be able to see results onscreen instantly, without the need to create a data extension first.

When it comes to your query, I am not sure why are you joining with the location table, and depending on what you want to achieve, you might have to use different joins. You might also try adding the ent. prefix to your synchronized data extensions:

SELECT 
o.Id as OutcomeId,
c.FirstName,
c.LastName,
c.Email,
c.Id
FROM ent.Outcome__c_Salesforce AS o 
INNER JOIN ent.LOCATIONCONTACT__C_SALESFORCE AS l 
ON l.Location__c = o.Location__c
INNER JOIN ent.Contact_Salesforce AS c 
ON c.Id = l.Contact__c
WHERE o.CreatedDate < DATEADD(hh, 6, getdate()) 
AND o.CreatedDate > DATEADD(hh, 4, getdate()) 
AND o.RecordTypeId = '0120W000001YbytQAC'

Take a look at different join types to determine the best one for your use case:

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.