2

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?

2
  • 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? Commented Sep 20, 2018 at 11:09
  • Hi Johannes, the relatienummer is the customer number :) Commented Sep 21, 2018 at 11:27

1 Answer 1

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.

2
  • I think w.Relatienummer and w.Aanmaakdatum should read m.Relatienummer and m.Aanmaakdatum Commented Feb 28, 2019 at 5:04
  • 1
    Fixed the aliases. Commented Feb 28, 2019 at 21:57

You must log in to answer this question.

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