0

I am trying to merge two DE (Accounts& Leads) into 1 Master DE. I want to exclude the leads already imported when importing the accounts. This is the SQL:

SELECT
    T1.Email AS EmailAddress, 
    T1.FirstName AS FirstName, 
    T1.LastName AS LastName,
    T1.Salesforce_ID_18_Digits_Lead__c AS Lead_ID, 
    T1.Id AS Salesforce_ID
FROM Lead_Salesforce T1
WHERE T1.Email IS NOT NULL 

UNION

SELECT 
    T2.PersonEmail AS EmailAddress, 
    T2.FirstName AS FirstName, 
    T2.LastName AS LastName,
    T2.Salesforce_ID_18_Digits_Accounts__c AS Account_ID, 
    T2.Id AS Salesforce_ID 
FROM Account_Salesforce T2 
WHERE NOT EXISTS (
    SELECT Salesforce_ID_18_Digits_Lead__c
    FROM Lead_Salesforce
    WHERE T2.Salesforce_ID_18_Digits_Accounts__c = Lead_Salesforce.Salesforce_ID_18_Digits_Lead__c
)
AND T2.Salesforce_ID_18_Digits_Accounts__c IS NOT NULL

The error I am getting is:

Converison failed when converting the nvarchar value '0010Y00000GsmrEQAR' to data type bit

The sfid is written from a text field into a text field. I am guessing the where not exist clause returns a bit value.

How can I avoid this problem?

  • Please add the details of your target data extension. – Adam Spriggs Jun 8 '17 at 10:59
  • @AdamSpriggs: Hi Adam, Email Adress is the primary key with length 255, FirstName & LastName are a text field (255) and Account_ and Lead_Id are a text field (255), salesforce_ID is a text field(1000). Do you need any other information? Thanks a lot! – user45509 Jun 8 '17 at 11:59
1

For sure, both select statements must include the same columns, even if they're not in common:

SELECT
    T1.Email AS EmailAddress, 
    T1.FirstName AS FirstName, 
    T1.LastName AS LastName,
    T1.Salesforce_ID_18_Digits_Lead__c AS Lead_ID,
    null as Account_ID, 
    T1.Id AS Salesforce_ID
FROM Lead_Salesforce T1
WHERE T1.Email IS NOT NULL 

UNION

SELECT 
    T2.PersonEmail AS EmailAddress, 
    T2.FirstName AS FirstName, 
    T2.LastName AS LastName,
    null as Lead_ID,
    T2.Salesforce_ID_18_Digits_Accounts__c AS Account_ID, 
    T2.Id AS Salesforce_ID 
FROM Account_Salesforce T2 
WHERE T2.Salesforce_ID_18_Digits_Accounts__c IS NOT NULL
AND NOT EXISTS (
    SELECT Salesforce_ID_18_Digits_Lead__c
    FROM Lead_Salesforce
    WHERE T2.Salesforce_ID_18_Digits_Accounts__c = Lead_Salesforce.Salesforce_ID_18_Digits_Lead__c
)

I'd also double-check your target data extension to ensure the datatypes your source columns in Lead_Salesforce and Account_Salesforce.

| improve this answer | |
  • Hi Adam, I added the fields as you mentioned above. If i run the activity now, i get a "Automation failed due to system error" notification. Thanks and best regards Saskia – user45509 Jun 8 '17 at 13:52
  • Adam's answer would work, but your question included the answer. One of your "18 char" fields has been defined in CRM as a Boolean. Secondly, is there a reason why your AccountID and LeadID should equal each other in your subquery. It doesn't make much sense. – Macca Jun 8 '17 at 15:13
  • @AndrewMcLeod: you are right, our subquery doesnt make sense here. What we are trying to achieve is that an email adress, which exists for the lead, is not included twice in our following communication. The code we used before was: WHERE T2.PersonEmail IS NOT NULL AND T2.PersonEmail NOT IN ( SELECT Email FROM Lead_Salesforce ), but we thought the identifier would not be unique in that case. Do you think a simple "select distinct" would help here? However, I dont't know how the 18char field could be a boolean field at all. – user45509 Jun 9 '17 at 8:16

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.