I know you said there are no duplicate emails, but it might be best to set up a dedupe subquery to validate this:
s.Magento_ID as Magento_ID,
when r.[Marketing Cloud_ID] is null or r.[Marketing Cloud_ID] = '' then s.EmailAddress
else r.[Marketing Cloud_ID] end as [Marketing Cloud_ID],
s.EmailAddress as ...
Sorry to bring this up... but it is a no go with LIKE. You probably need to get some other logic:
From the documentation:
Expression is true if the value in the specified fieldName matches the characters of the text string in the specified value. The LIKE operator in SOQL and SOSL is similar to the LIKE operator in SQL; it provides a mechanism for ...
You can query the data extension and assign a GUID to each record. Also take into account EazyE's question on whether or not it already has a Primary Key. I'm assuming the GUID won't be the PK since it doesn't exist on records before your change.
SELECT NewId() AS GUID
You could probably do it using a LIKE statement concatting the wildcards before and after the column.
e.g. click.LinkContent LIKE '%/' + prod.ProductID + '?%'
So full sample (with dummy fields, etc.)
FROM [ProductIDs] prod
INNER JOIN [_Click] click
ON click.LinkContent LIKE '%/' + prod.ProductID + '\?%'
In SOQL query you are not allowed to mix expression in where clause.
What you can add here is, you can create an extra formula field of type number which will have the sum of Salary__c and Bonus__c field value and then you can use the new field to compare.
Let's say the new field is Gross__c having formula (Salary__c + Bonus__c)
Now you can use it in query,
actual problem of your query is that you added count(COD_CID__c) in order by clause. i would recommend go through SOQL as per doc.
Note the following when using COUNT():
COUNT() must be the only element in the SELECT list.
You can use COUNT() with a LIMIT clause.
You can't use COUNT() with an ORDER BY clause. Use COUNT(fieldName) instead.
You can't ...
The data views are not directly related to your sendable data extension that you used for the send.
However, your sendable data extension has a column that is related to the subscriber key and therefore you can use that to join the data views. So you need another join to your sendable data extension to query that information.
Bookmark this - it will become your best friend
Marketing Cloud uses Data Views to store information about events that have to do with email. These views are kept for a soft 90-ish days before the tracking is archived on their side (depends on how much you send and to how many users =...
Upvote Spencer Curtis. I tried your solution and it worked nicely.
I was not as concerned about the randomness of the rows return so I was able to optimise the query's run time significantly by replacing NEWID() with ROW_NUMBER().
I also aliased the Data Extensions differently to more clearly delineate which one is which. Hopefully this adds a tiny bit ...
So you could do this non-programmatically with a plugin like DESelect and then build something that would look like this:
If you do want to write a query, I would indeed go with _Subscribers (_ListSubscribers is if you're using Lists instead of DEs and using that OOTB SFMC subscription center).
From [Newsletter List] a