In Marketing Cloud's Automation Studio, we keep getting an error for SQL we wrote that is supposed to combine the data from 2 separate SHARED data extensions in a child Business Unit. The error message that's prompted is the following

"An error occurred while checking the query syntax. Errors: The multi-part identifier "ent.France_Master_Permission.Federation_Id" could not be bound."

Below's the whole query:

SELECT ent.Country-1_Master_Contacts.TVCM_ID, 
FROM ent.Country-1_Master_Contacts
INNER JOIN ent.Country-1_Master_Permission
ON ent.Country-1_Master_Permission.Federation_Id 
= ent.Country-1_Master_Contacts.Contact_Key 
AND ent.Country-1_Master_Contacts.TVCM_ID 
= ent.Country-1_Master_Permission.TVCM_ID
WHERE Do_Not_Market_To = 'False' 
AND Unsubscribe_All = 'False' 
AND Email = 'False'

I think you need to use aliases. Change the query to the following:

SELECT mc.TVCM_ID, mc.Contact_Key, mc.Email_Address, mc.First_Name, mc.Last_Name, mc.BU_Code
    FROM ent.Country-1_Master_Contacts mc INNER JOIN ent.Country-1_Master_Permission mp
        ON mp.Federation_Id = mc.Contact_Key 
        AND mp.TVCM_ID = mc.TVCM_ID
    WHERE mp.Do_Not_Market_To = 'False' 
    AND mp.Unsubscribe_All = 'False' 
    AND mp.Email = 'False'

It also makes it a lot cleaner and more readable.

I assumed that the attributes in the where clause are from the Master_Permission Data Extension. As long as the attributes are unique over the two data extensions; there is no need to specify the alias. But I like to do it for consistency.

| improve this answer | |

I don't see the field "ent.France_Master_Permission.Federation_Id" in your query. Did you update your SQL ?

When I copy/paste your current SQL in a Query Activity, I have a syntax error. The use of SQL aliases resolves this issue.

I advice you to have a quick look on this page : SQL Reference

| improve this answer | |
  • Yes, it were aliases in the end. Tnx to both you guys. – nix9247 May 6 '19 at 15:03

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.