0

I have this SQL query and I cannot perform it due to violation of primary key.

The primary of the source data extensions are the following :

  • Account : Email
  • Opportunity : OpportunityId
  • Info_Magasin_AllSites : code_mag

The primary key of the target data extension is the Email

Can someone help me

Thanks a lot

Anne-Sophie

SELECT DISTINCT

account.FirstName                       as FirstName,
account.LastName                        as LastName, 
account.AccountID                       as AccountID, 
account.Email                           as Email, 
account.Mobile                          as Mobile, 
account.Subscriber_Key                  as Subscriber_Key, 
account.MobileCountryCode               as MobileCountryCode,

Opportunity.Etape                       as Etape, 
Opportunity.OpportunityId               as OpportunityId, 
Opportunity._CustomObjectKey            as ObjectKey,
Opportunity.Code_Magasin__c             as code_mag, 
Opportunity.MethodePaiement             as MethodePaiement, 
MAX(Opportunity.CreatedDate)            as CreatedDate, 
Opportunity.RaisonPerte                 as RaisonPerte, 
Opportunity.CloseDate                   as CloseDate,  
opportunity.Amount                      as Amount,
Opportunity.Produits                    as Produits,

Info_Magasin_AllSites.nom_mag                    as Magasin, 
Info_Magasin_AllSites.adr_mag                    as adr_mag, 
Info_Magasin_AllSites.nom_resp_magasin           as nom_resp_magasin, 
Info_Magasin_AllSites.tel_magasin                as tel_magasin, 
Info_Magasin_AllSites.email_magasin              as email_magasin, 
Info_Magasin_AllSites.Demande_avis_Google        as Demande_avis_Google,
Info_Magasin_AllSites.code_Postal                as code_Postal, 
Info_Magasin_AllSites.Lien_Google                as Lien_Google,
Info_Magasin_AllSites.Adresse_Google             as Adresse_Google


FROM AllAccount_DedupEmail_Last account 

INNER JOIN SelectOnly_AllOpportunity Opportunity 
ON account.AccountID = Opportunity.AccountId

INNER JOIN Info_Magasin_AllSites
ON Opportunity.Code_Magasin__c = Info_Magasin_AllSites.code_mag

WHERE Opportunity.Etape != 'Annulée' and Opportunity.Etape != 'Closed won'

group by account.FirstName, account.LastName, account.AccountID,  account.Email, account.Mobile, account.Subscriber_Key , account.MobileCountryCode, Opportunity.Etape, Opportunity.Code_Magasin__c, Opportunity.OpportunityId, Opportunity._CustomObjectKey , Opportunity.MethodePaiement, Opportunity.RaisonPerte, Opportunity.CloseDate, opportunity.Amount, Opportunity.Produits, Info_Magasin_AllSites.nom_mag, Info_Magasin_AllSites.adr_mag, Info_Magasin_AllSites.nom_resp_magasin, Info_Magasin_AllSites.tel_magasin, Info_Magasin_AllSites.email_magasin, Info_Magasin_AllSites.Demande_avis_Google, Info_Magasin_AllSites.code_Postal, Info_Magasin_AllSites.Lien_Google, Info_Magasin_AllSites.Adresse_Google
Improve this question
5
  • Please update your question to include the primary key fields in all of the data extensions involved -- including the target data extension.
    – Adam Spriggs
    Dec 2, 2020 at 14:32
  • Done :) thanks for your comment @AdamSpriggs
    – Anne-Sophie
    Dec 2, 2020 at 14:39
  • This group by statement is really really long. Are you sure you need all this grouping?
    – Johannes Schapdick
    Dec 2, 2020 at 14:48
  • I agree with you @JohannesSchapdick, I though that it will allow me to merge the duplicates of email but it is not working since I still have the error message. I thought about using a window but I do not know how to write it
    – Anne-Sophie
    Dec 2, 2020 at 14:52
  • The more group by clauses you are using the higher the row-count usually is, and the more likely you will face key constraints in your query. The windowing function would be the approach. You can find it in lot of answers that @AdamSpriggs has written. But maybe you can give this a try. Only use group by Account.Email, this would cause problems if you have different values or multiple values for the same column per email though => Research Adams posts and try to write a subselect statement ;)
    – Johannes Schapdick
    Dec 2, 2020 at 14:58

1 Answer 1

Reset to default
2

The easiest thing, in my opinion, is to use the T-SQL windowing functions to pick one of the duplicates with sort criteria. Simply set the partition by to match the primary key of your target DE.

The top 1 with ties selects the first row by a.email (the partition). What's first is determined by the order by o.CreatedDate desc (most recent by o.createdDate):

SELECT top 1 with ties
  a.FirstName
, a.LastName
, a.AccountID
, a.Email
, a.Mobile
, a.Subscriber_Key
, a.MobileCountryCode
, o.Etape
, o.OpportunityId
, o._CustomObjectKey ObjectKey
, o.Code_Magasin__c  code_mag
, o.MethodePaiement
, o.CreatedDate
, o.RaisonPerte
, o.CloseDate
, o.Amount
, o.Produits
, i.nom_mag Magasin
, i.adr_mag
, i.nom_resp_magasin
, i.tel_magasin
, i.email_magasin
, i.Demande_avis_Google
, i.code_Postal
, i.Lien_Google
, i.Adresse_Google
FROM AllAccount_DedupEmail_Last a
INNER JOIN SelectOnly_AllOpportunity o ON a.AccountID = o.AccountId
INNER JOIN Info_Magasin_AllSites i ON o.Code_Magasin__c = Info_Magasin_AllSites.code_mag
WHERE o.Etape != 'Annulée' and o.Etape != 'Closed won'
order by row_number() over (partition by a.Email order by o.CreatedDate desc)
Improve this answer
3
  • The Master of "Windows" :D
    – Johannes Schapdick
    Dec 2, 2020 at 15:00
  • it is working :D I am so happy ! I tried to find the answer since several days. Thank you so much for taking the time to find the answer @adamspriggs !
    – Anne-Sophie
    Dec 2, 2020 at 15:10
  • Thanks a lot also to @JohannesSchapdick for your time ! I appreciate a lot
    – Anne-Sophie
    Dec 2, 2020 at 15:10

You must log in to answer this question.

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