[SalesForce] Square Brackets sql query error

I am receiving an error:

The query syntax is invalid

Error while processing square brackets

I have tried to run it with and without brackets and still receive the same error. Please also note that the same query runs fine referencing FROM _Sent

SELECT s.SubscriberKey, ss.EmailAddress, MIN(s.EventDate) as 'FirstClickDate', o.ACCT_NUM, o.ACCT_NM, o.BUYER_CONTACT_UID, o.CONTACT_GUID, o.FIRST_NM, o.LAST_NM, o.EMAIL_ADDR, o.PHONE
FROM _Click s 
JOIN _job j ON j.JobID = s.JobID AND j.accountid = 1059291
JOIN ENT._subscribers ss ON s.SubscriberID = ss.SubscriberID
JOIN [MHAT_ImportDE] o ON s.SubscriberKey = o.CONTACT_GUID
WHERE (j.EmailName = ‘MHAT_Email1' and s.EventDate >= '8/5/2016')
Group by 
s.SubscriberKey, ss.EmailAddress, o.ACCT_NUM, 
o.ACCT_NM, o.BUYER_CONTACT_UID, o.CONTACT_GUID, o.FIRST_NM,
o.LAST_NM, o.EMAIL_ADDR, o.PHONE

Best Answer

You've got a curly quote in there: ‘MHAT_Email1'

SELECT 
      s.SubscriberKey
    , ss.EmailAddress
    , MIN(s.EventDate) as 'FirstClickDate'
    , o.ACCT_NUM
    , o.ACCT_NM
    , o.BUYER_CONTACT_UID
    , o.CONTACT_GUID
    , o.FIRST_NM
    , o.LAST_NM
    , o.EMAIL_ADDR
    , o.PHONE
FROM _Click s 
JOIN _job j ON (j.JobID = s.JobID AND j.accountid = 1059291)
JOIN ENT._subscribers ss ON (s.SubscriberID = ss.SubscriberID)
JOIN [MHAT_ImportDE] o ON s.SubscriberKey = o.CONTACT_GUID
WHERE 
    j.EmailName = 'MHAT_Email1'
    and s.EventDate >= '8/5/2016'
Group by 
    s.SubscriberKey
    , ss.EmailAddress
    , o.ACCT_NUM
    , o.ACCT_NM
    , o.BUYER_CONTACT_UID
    , o.CONTACT_GUID
    , o.FIRST_NM
    , o.LAST_NM
    , o.EMAIL_ADDR
    , o.PHONE

A word of caution. Joining to all of the system data views in a single query is a recipe for a timeout. I'd recommend proactively giving each data view it's own query.

Related Topic