SQL Error: Old style JOIN (ANSI JOINs) syntax is not allowed [closed]

Question

I have the following SQL query activity in Marketing Cloud, which is producing an error: Old style JOIN (ANSI JOINs) syntax is not allowed.

SELECT CASE SurveyResponse
        WHEN ''
            THEN 'N/A'
        ELSE SurveyResponse
        END AS SurveyResponse
    ,(CONVERT(DECIMAL(3, 1), COUNT(*) * 100.0 / (SELECT COUNT(SubscriberKey) FROM DEName) )) as ResponsePercentage
    ,(SELECT COUNT(SubscriberKey) FROM DEName) as TotalUnsubscribes
FROM DEName
GROUP BY SurveyResponse

I don't understand why the SELECT COUNT(SubscriberKey) FROM DEName statement works in the last clause, but I can't use the same subquery for my "give me a percentage" clause?

Any ideas?

Thanks!

Answer 1

In SFMC, you can use an OUTER APPLY. That should work for your scenario:

SELECT 
  CASE de1.SurveyResponse
      WHEN '' THEN 'N/A'
      ELSE SurveyResponse
  END AS SurveyResponse
  , (CONVERT(DECIMAL(3, 1), COUNT(*) * 100.0 / (x.totalCount) )) AS ResponsePercentage
  , x.totalCount AS TotalUnsubscribes
FROM DEName AS de1
OUTER APPLY (
  SELECT 
     COUNT(de0.SubscriberKey) totalCount
  FROM DEName AS de0
  WHERE de0.subscriberkey = de1.subscriberkey
) x
GROUP BY x.SurveyResponse, x.totalCount