2

I have a primary key violation occurring where there is no duplicate record within the target extension, nor the source extension (which is a synchronized data extension).

Data Action type is Update

Query also contains SELECT DISTINCT

Wondering why automation is throwing that code.

Idea: This data extension was originally used to test before launching in Journey Builder. There were a few times where I cleared records. Is there a known issue associated with data still potentially being in the extension (even though it's been cleared)?

  SELECT DISTINCT
sf_contact.id AS contactid,
sf_case.contact_email__c AS contact_email,
sf_case.contact_full_name__c AS contact_full_name,
sf_contact.firstname AS contact_first_name,
sf_contact.lastname AS contact_last_name,
sf_case.id AS case_id,
sf_case.RecordTypeName__c AS case_type,
sf_case.ownerid AS case_owner_id,
sf_user.email AS case_owner_email,
sf_user.name AS case_owner_full_name, 
sf_user.firstname AS case_owner_first_name,
sf_user.lastname AS case_owner_last_name,
sf_case.status AS case_status,
sf_case.kickoff_call__c AS kickoff_call,
sf_case.gameplan_url__c AS gameplan_url,
sf_case.Site_Shell_URL_Text__c AS siteshell_url,
sf_case.Registration_URL__c AS registration_url,
sf_case.Last_Session_Created__c AS last_session_created,
sf_case.First_Session_Status__c AS first_session_status,
sf_case.first_usage_date__c AS first_usage_date,
sf_case.case_age__c AS case_age,
sf_case.organization__c AS organization_id,
sf_case.Organization_Name__c AS organization_name,
sf_case.Anticipated_Usage_Date_AUD__c AS anticipated_usage_date,
sf_orgs.Processor_Settings_Status__c AS processor_settings_status,
sf_orgs.Bank_Account_Status__c AS bank_account_status,
sf_case.Registration_Project_Status__c AS registration_project_status,
sf_orgs.Boss_Organization_Id_Text__c AS boss_id,
sf_org_prod.Sold_On__c as sold_on,
sf_case.accountid AS account_id,
sf_case.Organization_Product__c AS organization_product_id,
sf_user.phone AS case_owner_phone,
sf_orgs.country__c AS organization_country,
sf_case.Automated_Messaging_Opt_Out__c AS opt_out,
sf_session.Session_Id__c AS session_id,
sf_session.Status__c AS session_status,
sf_contact.id AS contact_id,
sf_account.name AS account_name


FROM ENT.[Case_salesforce] sf_case
LEFT JOIN ENT.[Org_Product__c_Salesforce] sf_org_prod ON sf_org_prod.id = sf_case.Organization_Product__c
INNER JOIN ENT.[User_Salesforce] sf_user ON sf_case.ownerid = sf_user.id
INNER JOIN ENT.[contact_salesforce] sf_contact ON sf_case.contactid = sf_contact.id
INNER JOIN ENT.[ORGANIZATION__C_SALESFORCE] sf_orgs ON sf_case.organization__c = sf_orgs.id
INNER JOIN ENT.[Account_Salesforce] sf_account ON sf_case.accountid = sf_account.id
LEFT JOIN ENT.[Session__c_Salesforce] sf_session ON sf_org_prod.id = sf_session.Organization_Product__c


WHERE
sf_case.RecordTypeName__c = 'Onboarding Project'
AND 
sf_org_prod.Sold_On__c >= DATEADD(day, -20, cast(GETDATE() AS DATE))
AND 
sf_org_prod.Sold_On__c <= DATEADD(day, -11, cast(GETDATE() AS DATE))
AND
(sf_case.status = 'new'
OR
sf_case.status = 'in progress')
AND
(sf_user.email = 'emailsample@sample.com'
  • Are you able to post the query you're using? – Amtera Apr 26 '18 at 15:26
  • Added query to question – Tom Callahan Apr 26 '18 at 15:28
  • Wow that is some query @TomCallahan I feel like the query is less useful that the DE, what columns have you marked as Primary Key in the Data Extension? – garek007 Apr 26 '18 at 15:43
  • Only PK is case_id. Which are all unique in SalesForce – Tom Callahan Apr 26 '18 at 16:21
2

Multiple-table joins could produce duplicate rows. Instead of Select Distinct, have you tried eliminating duplicate rows using the ROW_NUMBER() OVER(PARTITION BY [Primary Key] ORDER BY [Columns]) clause?

    SELECT x.*
FROM

(SELECT
sf_contact.id AS contactid,
sf_case.contact_email__c AS contact_email,
sf_case.contact_full_name__c AS contact_full_name,
sf_contact.firstname AS contact_first_name,
sf_contact.lastname AS contact_last_name,
sf_case.id AS case_id,  
row_number()over(partition by sf_case.id ORDER BY sf_case.id DESC) AS row
sf_case.RecordTypeName__c AS case_type,
sf_case.ownerid AS case_owner_id,
sf_user.email AS case_owner_email,
sf_user.name AS case_owner_full_name, 
sf_user.firstname AS case_owner_first_name,
sf_user.lastname AS case_owner_last_name,
sf_case.status AS case_status,
sf_case.kickoff_call__c AS kickoff_call,
sf_case.gameplan_url__c AS gameplan_url,
sf_case.Site_Shell_URL_Text__c AS siteshell_url,
sf_case.Registration_URL__c AS registration_url,
sf_case.Last_Session_Created__c AS last_session_created,
sf_case.First_Session_Status__c AS first_session_status,
sf_case.first_usage_date__c AS first_usage_date,
sf_case.case_age__c AS case_age,
sf_case.organization__c AS organization_id,
sf_case.Organization_Name__c AS organization_name,
sf_case.Anticipated_Usage_Date_AUD__c AS anticipated_usage_date,
sf_orgs.Processor_Settings_Status__c AS processor_settings_status,
sf_orgs.Bank_Account_Status__c AS bank_account_status,
sf_case.Registration_Project_Status__c AS registration_project_status,
sf_orgs.Boss_Organization_Id_Text__c AS boss_id,
sf_org_prod.Sold_On__c as sold_on,
sf_case.accountid AS account_id,
sf_case.Organization_Product__c AS organization_product_id,
sf_user.phone AS case_owner_phone,
sf_orgs.country__c AS organization_country,
sf_case.Automated_Messaging_Opt_Out__c AS opt_out,
sf_session.Session_Id__c AS session_id,
sf_session.Status__c AS session_status,
sf_contact.id AS contact_id,
sf_account.name AS account_name


FROM ENT.[Case_salesforce] sf_case
LEFT JOIN ENT.[Org_Product__c_Salesforce] sf_org_prod ON sf_org_prod.id = sf_case.Organization_Product__c
INNER JOIN ENT.[User_Salesforce] sf_user ON sf_case.ownerid = sf_user.id
INNER JOIN ENT.[contact_salesforce] sf_contact ON sf_case.contactid = sf_contact.id
INNER JOIN ENT.[ORGANIZATION__C_SALESFORCE] sf_orgs ON sf_case.organization__c = sf_orgs.id
INNER JOIN ENT.[Account_Salesforce] sf_account ON sf_case.accountid = sf_account.id
LEFT JOIN ENT.[Session__c_Salesforce] sf_session ON sf_org_prod.id = sf_session.Organization_Product__c


WHERE
sf_case.RecordTypeName__c = 'Onboarding Project'
AND 
sf_org_prod.Sold_On__c >= DATEADD(day, -20, cast(GETDATE() AS DATE))
AND 
sf_org_prod.Sold_On__c <= DATEADD(day, -11, cast(GETDATE() AS DATE))
AND
(sf_case.status = 'new'
OR
sf_case.status = 'in progress')
AND
(sf_user.email = 'email@email.com'
OR 
sf_user.email = 'email@email.com'
OR
sf_user.email = 'email@email.com'
OR
sf_user.email = 'email@email.com'
OR
sf_user.email = 'semail@email.com'
OR
sf_user.email = 'email@email.com'
OR
sf_user.email = 'email@email.com'
OR
sf_user.email = 'email@email.com'
OR
sf_user.email = 'email@emaile.com'
) 
) x

WHERE x.row = 1
  • 1
    This is the route I'll have to go. Updating your snippet with the one I'm using that appears to be fixed. – Tom Callahan Apr 27 '18 at 15:14

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.