New answers tagged query
0
@Gortonington's answer regarding Three-Valued Logic solved the problem. Putting some additional info in an answer here for the sake of formatting.
Specifically in this case I used a CASE statement that looked like this:
SELECT *
FROM [TABLE A] a
LEFT JOIN [TABLE B] b
ON a.ContactKey = b.SubscriberKey
WHERE a.Seasonal_Gift_Inspiration = 1
AND b....
2
The issue you are facing is that SQL uses Three-Valued Logic, which means there is True, False and Unknown(NULL). A value that is NULL therefore cannot be true or false, which causes issues with Not Equals conditional statements in SQL.
For instance, lets say TableA has values in ColumnA of 1|2|NULL and you use the following SQL:
SELECT *
FROM TableA
...
4
Your first query does not include the _Open data view, so it won't work for your use case.
If you want to display all subscribers who opened an email, you can use the following, bearing in mind what Lukas said - Data Views only hold last 6 months worth of tracking data:
SELECT
s.SubscriberKey,
j.EmailName,
s.EventDate as SentDate,
o.EventDate as ...
0
In your second query, write C.ContactKey is not NULL. Something like
SELECT *
FROM [TABLE A] a
LEFT JOIN [TABLE B] b
ON a.ContactKey = b.SubscriberKey
LEFT JOIN [TABLE C] c
ON a.ContactKey = c.ContactKey
WHERE b.SubscriberKey IS NULL
AND c.ContactKey IS not NULL
Also for consistent results, ensure that you filter out NL records in your query to populate ...
3
It's a Date Literal. You need to merge it in as such. Rather than using a bind variable, you need to actually include the string so it results in your dynamic SOQL containing a Date Literal.
String soql = '... WHERE CreatedDate = LAST_N_MONTS:' + x;
Effectively, what you had would output:
SELECT ... FROM ... WHERE CreatedDate = 'LAST_N_MONTHS:42'
But ...
0
If your target DE have SubscriberKey as primary key, you should certainly apply a partition to the result to avoid a Primary Key violation error.
SELECT LinkName,
SubscriberKey
FROM
(SELECT LinkName,
c.SubscriberKey,
eventdate,
ROW_NUMBER() OVER (PARTITION BY c.SubscriberKey
ORDER BY ...
4
The issue might be that the date format of the two is not matching correctly.
Try using:
DATEDIFF(year, CAST(creation_date__c as DATE), CAST(getdate() as DATE))
So that you cast each as identical data types, which should remove any possible data mismatch.
2
If your field creation_date__c is of the correct type, date, this should fulfil your use case:
SELECT
DATEDIFF(year, creation_date__c, getdate()) AS AnniversaryYears
FROM
DataExtensionName
Result:
The difference between GETDATE() and SYSDATETIMEOFFSET() is that the latter will just add the time zone, which won't be comparable to creation_date__c since it ...
0
As Keith stated, use binding would be better as it also avoids injection.
I'd recommend building such dynamic queries using a library such as Query.apex. Using this library, your statement becomes:
List<Lead> leads = new Query('Lead')
.selectFields('Company')
.addConditionLike('Company', '%compagnia%')
.run();
This would also solve injection ...
3
You can create filtered lists in Mobile Connect that will be available as a source to standard SQL queries.
The method of obtaining those contacts is described here:
Permanently remove Contacts or Subscribers and associated data from Contact Builder
Go into Mobile Connect:
Click Manage and in the List tab create a filtered list
You will be asked to ...
0
I was having the same issue and came across this post, but kept digging and somebody on the partner community shared this with me:
Update Subscriber Status in Marketing Cloud
This solution is pretty simple and worked perfectly for my use case. I just exported the DE, updated the CSV, and imported it to All Subscribers. Easy peasy.
Top 50 recent answers are included
