0

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
  • Probably you need to check this on the appropriate forum as this one is focused on Salesforce. – Oles Malkov Aug 18 '16 at 14:04
  • 1
    I suspect that this part s.EventDate >= '8/5/2016' could be the culprit. – abhi Aug 18 '16 at 14:17
  • There are no Joins (Like that) in SOQL. If this is SQL then you are on the wrong forum – Eric Aug 18 '16 at 14:29
  • Even though it is happening in the marketing cloud? – Tamara Thompson Aug 18 '16 at 14:32
  • 1
    Tagging is key. I added the marketing-cloud tag for you so the context is more clear. – Adrian Larson Aug 18 '16 at 14:48
4

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.

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.