Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It only takes a minute to sign up.
Sign up to join this community
I am trying to make an SQL query from a data extension with a data view, but i can't get it to work.
SELECT
a.id as Id
, a.email as email
, a.FirstName as FirstName
from contact_salesforce a
left join _Open o
on o.SubscriberKey = a.id
it gives an error when i do the automation:
"Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object"
Data Extension fields:
Id Text 18
Email EmailAddress 254
FirstName Text 40
The query specified will return multiple rows if more than one open event is found for the particular subscriber. You can add the keyword distinct to your query to insure only unique row of data are returned by your query. This will eliminate the key violation.
SELECT distinct
a.id as Id
, a.email as email
, a.FirstName as FirstName
from contact_salesforce a
left join _Open o
on o.SubscriberKey = a.id
Normally, I avoid using the distinct keyword and opt for aggregating the query results in some way. But since this query is only selecting columns from the contact_salesforce data extension, using distinct will work in this case.
SELECT distinct
a.id as Id
, a.email as email
, a.FirstName as FirstName
from contact_salesforce a
left join _Open o
on o.SubscriberKey = a.id
