4
Marketing Cloud does only offer a reduced scope of SQL functionality in Automation Studio's query activities. To quote from the MC SQL reference:
SQL support for the SQL Query Activity is based on, but doesn’t
precisely correspond to, SQL Server 2016 capabilities.
Hence you will need to adjust your query to produce the needed result:
SELECT First_name, ...
3
I've previously outlined my go-to for timeout mitigation here.
You can use the hidden _customObjectKey column in every DE and the % mod operator to split the processing of your query into multiple queries. For your query, you could split it into 3 queries like this:
Query 1
SELECT
Sub_ID
, Field1
, Field2
, Field3
, Field4
, Field5
, Field6
, Field7
, ...
2
If you mean "de-duplicate by email address" when you say "limit it to just one record", then you can do something like this:
SELECT top 1 with ties
s.email
, c.SubscriberKey
FROM InputDE s
INNER JOIN ent._Subscribers c ON c.EmailAddress = s.email
order by row_number() over (paritition by s.email order by s._customObjectKey desc)
This ...
2
I think you're missing a comma before the CASE statement. Here's how I'd format it:
SELECT
SubscriberKey
, EmailAddress
, [First Name]
, [Last Name]
, [CAT-Newsletter]
, [CAT-Legislative Update] Legislative_and_Regulatory_Updates__c
, ModifiedDate
, [CAT-Webinars]
, [CAT-Product Service] Products_and_Services_Update__c
, [CAT-Research] ...
Only top voted, non community-wiki answers of a minimum length are eligible
