Marketing Cloud query activity syntax error

Question

I've recently tried to create a query activity.

Our Marketing Cloud is connected to our Salesforce via Marketing Cloud Connector, and we are syncing contact data.

I'm trying to flag our contacts who ever hard bounced.

So I assembled the following sql:

SELECT 
      Id as SubscriberKey, 
      channel__c as Channel, 
      (
         (SELECT count(*) FROM _Bounce b 
          WHERE b.SubscriberKey = csf.Id AND b.BounceCategory = 'Hard bounce') > 0
      ) as HasHardBounced
FROM Contact_Salesforce csf

My experience with SQL tells me this is valid, and sure enough I tried this with SQL developer, and it worked. But in Marketing Cloud it gives me an error:

"An error occurred while checking the query syntax. Errors: Incorrect syntax near '>'."

Any ide why it not letting me use an operator there? Also any other way to achieve what I was trying?

Thanks for your help!

Answer 1

There's no aggregation/grouping going on in your query there to do the "> 0" evaluation.

Try this:

SELECT 
      csf.Id as SubscriberKey, 
      csf.channel__c as Channel, 
      COUNT(1) as HasHardBounced
FROM
    Contact_Salesforce csf INNER JOIN
    _Bounce b WITH (NOLOCK)
        ON  b.SubscriberKey = csf.Id
WHERE
    b.BounceCategory = 'Hard bounce'
GROUP BY
    csf.Id
HAVING
    COUNT(1) > 0