Hot answers tagged

8

This occurs because of an optimization during the "query planning" phase of a query. The query optimizer removes null values from filters on required fields. This optimization allows Salesforce to use standard indexes instead of a full table scan on those fields, which can improve performance by many orders of magnitude on large tables. If Salesforce fixed ...


7

The answer is NO A far better practice is to store API names in custom settings/custom metadata and query for the resource by apiName in the code: API Names are more transparent than IDs Custom metadata values can be deployed Sandboxes created from PROD that include ID values might be broken if the IDs point at data records not present in the sandbox (such ...


6

If your target Data Extension's primary key is just Subscriber Key, I'd expect a primary key violation since you're joining with a one-to-many relationship (MasterSubscribers -> _Click). I think you can achieve what you want without a JOIN: select m.SubscriberKey , m.EmailAddress , m.Country , m.Language , m.Gender , m.FirstName , m.LastName , m....


6

I would look into using Query Studio which is much more user-friendly and better for trial and error development work. Query Studio. It will help you as you learn and get up to speed. I'd also really recommend reviewing the query/SQL documentation that SFMC has provided because it will help understand the limitations of SQL in SFMC vs the outside world. ...


5

My answer is that it doesn't matter for this example as the volume of data you are dealing with - 340 records - is small so optimization is way down the priorities. My priority list is ballpark this if there is nothing extreme about the code: Code that works Tests that prove the code works Code that doesn't run into query or CPU or heap governor limits Code ...


5

One possible solution would be to write a query to copy everything from your data extension into a new data extension. You could then check the new data extension against the original to see if any values have changed. This could all be run inside of an automation to run at an interval of your choosing. The first query in the automation will check to see if ...


5

You've got the right idea just the wrong syntax for the semi-join (not technically an inner query). Try this: List<Contact> conList = [ SELECT id, name FROM Contact WHERE id IN (SELECT contactId FROM Opportunity WHERE StageName = 'Closed Won') ];


4

I know you said there are no duplicate emails, but it might be best to set up a dedupe subquery to validate this: SELECT a.* FROM ( select s.Magento_ID as Magento_ID, case 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 ...


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.


4

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.) SELECT prod.ProductID , yourOtherFields FROM [ProductIDs] prod INNER JOIN [_Click] click ON click.LinkContent LIKE '%/' + prod.ProductID + '\?%' WHERE click....


4

Unfortunately you cannot target a filtered Data Extension with a query. These are able to be manipulated by your filter only. If you cannot utilize the filter to get what you need, you will need to create a new data extension to target with the query. If your filter is appropriate/sufficient and you just want to automate it, you can utilize a filter ...


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 ...


4

You do not have to convert the string to lookup field, the lookup Id is a string: Id neededId = Id.valueOf(msg.to_WhatId__c); String objectAPIName = neededId.getSobjectType().getDescribe().name; if(objectAPIName == 'Gear__c'){ // assign the lookup value msg.Gear__c = neededId; } If for some business purpose, you need to refactor the code to avoid ...


4

The first answer is entirely correct, BUT the solution has become much easier lately, with the introduction of the new data view businessUnitUnsubscribes, which eliminates the need for the (correct) workaround linked by Brad. https://help.salesforce.com/apex/HTViewHelpDoc?id=mc_as_data_view_businessunitunsubscribes.htm&language=en_us Hope this helps! ...


4

You need to run this in the ENT BU to get records, as it sounds that you might have a subscriber filter configured. When you send an email to a data extension, the contacts will always be added to All Subscribers in ENT BU. These are shared across the enterprise, however you can manage subscribers so they only appear at a specific child business unit level. ...


4

The ordering is being done appropriately. The issue is that you're thinking that the Id and String types are equivalent to one another. If you disregard the last 3 characters of the Id, or change your a and b variables to be the appropriate type (i.e. Id, not String), then you'll see that your test returns the same result as the query that is, Id a < ...


3

There is no way to load more than a total of 50K records in a given synchronous session/transaction (as per the documentation), and certainly no way to do this in a trigger. The only way to deal with this sort of thing is to use async processing such as in a Batchable as you mentioned. It is possible to execute certain types of aggregate query across more ...


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 ...


3

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 FROM DataExtension


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 ...


3

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 matching ...


3

A typical "error" leading to timeouts is that there simply are too many JOINs over the tables in the backend (data views). Solution would be to split up your query into several sequential parts. Often, this behavior will "creep" into your project as the data views are very small at the beginning, and with increased sendout volume, problems begin. So, my ...


3

The total number of parameters you need to use in JavaScript will be the number of Apex parameters plus one for the callback. You neglected to include the required parameter, hence the error. The following change would suffice: BudgetGraphController.getBudgetData('{!JSENCODE($CurrentPage.paramEters.Id)}', function (result, event) { Note that: global ...


3

Before triggers only allow you to avoid DML for the records taking part in the trigger (i.e. in trigger.new and/or trigger.newMap). Everything else still needs explicit DML.


3

Row_Number() is one of the more useful functions for everything relating to deduping. This will number your duplicates and from the result of the numbering, select all those that do not have the number 1, i.e. are duplicates. Code is untested, but extrapolated from a working example. I hope it gives you a starting point. SELECT a.EmailAddress,a.SubscriberKey ...


2

The search indexes require the first character of each "word" (in your case, numbers) in order to make a match. This is a limitation of the search engine's indexes, and you can't work around it aside from making sure you have all permutations you want to have searchable. See FIND {SearchQuery} for more details on wild cards and what partial searches are ...


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 ...


2

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, ...


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 ...


2

You can get all your journeys history through interactions undocumented REST API. First of all, you need to get your journey's Definition ID via a GET call to: /interaction/v1/interactions/?name=JOURNEY_NAME The Definition ID is located inside a nested array called items. Then, through a POST call to the endpoint below, you can get all you journeys ...


Only top voted, non community-wiki answers of a minimum length are eligible