1

Every day a file is dropped on an FTP which consists of people with their relationnumbers (relatienummer) and the products (documentnummer) that they have bought. However, the same person can buy multiple products. I have a journey that is only for people that buy 1 specific product in the file and I want to select these people and insert them into a Data Extension in which relation number is the identifier.

This is my query:

SELECT DISTINCT m.Relatienummer, 
       m.Aanmaakdatum,
       m.Documentnummer
FROM Acquisitie_Campagne_Testrapporten_PROD m
WHERE  Artikelcode = 'DOS006' AND m.Aanmaakdatum = Cast(Dateadd(dd, -1, Getdate())as date)

Query failed during execution.

Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Acquisitie_Campagne_Main_Contacts_PROD'. The duplicate key value is (0001423---).

Can someone help me with this error?

  • What is the relatienummer, is that a Customer Number or the unique identifier for a purchase? If its a customer number what happens if someone buys this product twice in different purchases, will that violate the unique constraint? – Johannes Schapdick Sep 20 '18 at 11:09
  • Hi Johannes, the relatienummer is the customer number :) – Ivor Bergmans Sep 21 '18 at 11:27
2

Generally speaking you'd use a T-SQL windowing function to de-duplicate the rows.

If your primary key in the target data extension is only Relatienummer, then you can partition by that field and order the duplicates by Aanmaakdatum. The row_number() numbers them in order. The where-clause in the outer query picks the first one for each Relatienummer.

select 
  x.Relatienummer
, x.Aanmaakdatum
, x.Documentnummer
from (
    SELECT 
      m.Relatienummer
    , m.Aanmaakdatum,
    , m.Documentnummer
    , row_number() over (partition by m.Relatienummer order by m.Aanmaakdatum asc) ranking
    FROM Acquisitie_Campagne_Testrapporten_PROD m
    WHERE  
    m.Artikelcode = 'DOS006' 
    and m.Aanmaakdatum >= convert(date,getDate()-1)
    and m.Aanmaakdatum < convert(date,getDate())
) x 
where x.ranking = 1

I have a list of fixes for the 4 types of SFMC query errors on my blog.

  • I think w.Relatienummer and w.Aanmaakdatum should read m.Relatienummer and m.Aanmaakdatum – Eliot Harper Feb 28 at 5:04
  • 1
    Fixed the aliases. – Adam Spriggs Feb 28 at 21:57

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.