1

I have 1st DataExtension names "Accessories" and 2nd as "TestAccessories". I want to dump all the unique records from Accessories to TestAccessories, My query is as follows:

SELECT DISTINCT [Customer Code], Email, Location, [Operator Code], Date, Time, [Customer Group1], GUID
FROM Accessories

In Accessories DE GUID is Primary key, and I want unique values from [customer code] field, the above given query is not giving me unique values.What is the other way?

I have tried the same query for finding Distinct emails but it's not giving required output.

4
  • Have you tried using just one field, the one that needs to be unique, like SELECT DISTINCT [Customer Code] FROM Accessories Mar 22, 2018 at 6:26
  • Yes, I did try this also but it's not working as required.
    – Prisha
    Mar 22, 2018 at 7:39
  • I have tried it. This works for me: SELECT DISTINCT Field as UUID FROM dataExtension1. The as UUID is only to match it on the field on the target data extension Mar 22, 2018 at 8:09
  • GUID's are already unique fields so its of no use to filter data based on GUID.
    – Prisha
    Mar 22, 2018 at 11:48

1 Answer 1

1

AFAIK you will get every item out of this because you use distinct over all of your fields instead of only one column.

Workaround, because i dont know the better way atm:

  1. Create a DataExtension called DISTINCTCUSTOMERCODES with the field CustomerCode
  2. Create an SQL Query Automation SELECT DISTINCT [Customer Code] as CustomerCode FROM Accessories into the new Data Extension DISTINCTCUSTOMERCODES
  3. Create a Second SQL Query ActivitySELECT DISTINCT [Customer Code], Email, Location, [Operator Code], Date, Time, [Customer Group1], GUID FROM Accessories as acc INNER JOIN DISTINCTCUSTOMERCODES as dcc ON acc.CustomerCode= dcc.CustomerCode
  4. Create an Automation that runs those activities after each other

I know that there is a better solution for the SQL Statement. I believe (not certain) you have to do something like this. I know the Syntax is wrong on the JOIN statement atleast, feel free to correct this.

SELECT [Customer Code], Email, Location, [Operator Code], Date, Time, [Customer Group1], GUID
FROM Accessories as acc1
INNER JOIN Accessories as acc2
(SELECT DISTINCT [Customer Code]
FROM Accessories as acc2)
ON acc1.[Customer Code]= acc2.[Customer Code]
3
  • This isn't useful because I have only 1 data extension which contains records and I want to dump the data in the 2nd data extension. ('Customer Code" is a field in the data extension).
    – Prisha
    Mar 22, 2018 at 11:47
  • Got one mistake in one query in my workaround. Are you sure that you dont want to create a new dataextension like i Said in the workaround? It is working and i tested it aswell. Mar 23, 2018 at 4:20
  • @Prisha you need only query the [Customer Code] filed or GUID field also if you need only [Customer Code] field means use this query like this Select DISTINCT Customer Code from Accessories its working fine
    – Ravi.A
    Mar 23, 2018 at 7:47

You must log in to answer this question.

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