Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.

Questions tagged [sql]

Structured Query Language (SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS)

Filter by
Sorted by
Tagged with
-4 votes
0 answers
25 views

How to query records of children with ages above 2yrs (there will be 2 dob columns).. Dob will be in years [closed]

Eg DOB1 2019 DOB2 2017 I have used the query Where Dob > dateadd(year, - 2,getdate()) But it's not working properly
user avatar
-1 votes
1 answer
30 views

SQL query where my child dates should be greater than 3 years from the current date [closed]

I have three date columns My child Date and I want those records in which MyDate should be greater than current date by 3 years. Based on child dob year how can i get the below 3 years records only ...
user avatar
  • 345
0 votes
1 answer
30 views

Violation of primary key constraint

I have two data extensions. The first data extension called ClientDE has two primary keys - ClientID and LastPurchaseDate. The second data extension called LastPurchaseDE has one primary key - ...
user avatar
0 votes
0 answers
38 views

SQL _Click data for part of a URL string

I'm trying to query the last 6 months of clicks for a URL in SFMC. Because we add UTMs and parameter strings to the base URL, it changes in each email. I'm not having much luck using 'LIKE' but I am ...
user avatar
0 votes
0 answers
23 views

SQL Query - Exist function

Could you please trouble shoot the below query for me. The exist part of the subquery doesn't seem to work as i would expect, when i run the sub query independently some of the 'A.ID' do not show up ...
user avatar
-1 votes
1 answer
26 views

Marketing Cloud SQL To Convert Epoch (string) to Date Time? [closed]

I am trying to convert from Epoch Date to Regular date. I tried the below SQL but it didn't work. SELECT epdate, convert(varchar, convert(date, epdate, 105), 101) AS epdate FROM tablea Any idea ...
user avatar
  • 525
0 votes
1 answer
30 views

Sql query to exclude related records in dataset

I am attempting to create an sql query in salesforce marketing cloud...I have a list of email addresses, the number of products they own, and the associated product codes. I would like to set up the ...
user avatar
-1 votes
1 answer
26 views

SOQL: How to get the biggest value from a field and compare to a another on query?

I need to get the biggest value from field createddate and then compare this value to another date on a query/soql. I think about using "order by createddate desc limit 1" but I don't know ...
user avatar
  • 1
0 votes
1 answer
51 views

Query Question (automation failed due to system error)

THe code below is throwing an error "failed due to system error" - is it because of the subquery? I double checked my DE's data types and length - looks good but this is throwing an error ...
user avatar
  • 111
0 votes
1 answer
37 views

ERROR: Query failed: Timeout in automation studio

I am running the query many times and keep receiving Time out error. Please help me to solve the issue. Below is my sql query. SELECT Opp.Id AS OpportunityId, payer.id as contactid, Payer....
user avatar
  • 59
0 votes
1 answer
35 views

String to Date in SQL

I have the following problem: I imported a csv file in MC and there is a date field in it. When I create the billing_date attribute as text field, all of the csv data will be imported correctly and ...
user avatar
  • 1
0 votes
1 answer
24 views

Retrieve data from synchronised data extension to a sendlog data extension

I would like to retrieve the sends and some data from Synchronised Data Extension in a sendlog data extension. Indeed, more than the sends I would like to retrieve : the contacts ID who have received ...
user avatar
  • 27
0 votes
0 answers
39 views

SFMC Data views for all subscriber clicks all time (inclusive of journeys)

Can someone please assist in writing a SQL query in automation studio to pull SFMC data views. I would like to query all users who have clicked a particular URL type across all emails sent in the SFMC ...
user avatar
0 votes
0 answers
22 views

common.exception.SfdcSqlException: ORA-30006: resource busy; acquire with WAIT timeout expired [duplicate]

I am trying to update the CaseOwner field using a scheduled flow. The flow is able to update the CaseOwner Field for few cases. And for few cases, I am facing the following error in Production. In ...
user avatar
  • 1
0 votes
0 answers
56 views

how to find out total number of subscribers who were sent 1 email in last from in salesforce marketing cloud

I am trying to get a count of all subscribers (irrespective of BU) - hence I am running the query in parent BU who received ONLY 1 email in previous month. This is my query but it is not returning any ...
user avatar
  • 99
0 votes
1 answer
64 views

SQL Error - "Field name cannot be blank"

We are trying to run the following query: SELECT a.nihrm__BookingContact__c as "BOOKING_CONTACT", a.Name as "ACCOUNT_NAME", b.Email as "EMAIL", b.FirstName as "...
user avatar
  • 1
0 votes
1 answer
64 views

Query to Combine Data from 3 different Data View Tables

I've pulled the last 30 days of data from 3 data view tables (Job, Open, and Click) into 3 different data extensions. I'm attempting to run a query to dump data from my 3 different data extensions ...
user avatar
0 votes
1 answer
45 views

SQL Query on a list gives 'Error saving the Query field.Invalid column name'

In Query Studio i'm trying this just to find out how to proceed further: SELECT status ,[Email Address] ,CountryCode ,LanguageCode FROM _listSubscribers WHERE ListID = 7989 AND [Subscriber Key] = '...
user avatar
0 votes
1 answer
29 views

SQL to see records if their value in one column has changed

I have the following four columns: DATE_OF_FIRST_SUB_AEST (the first time a person purchase a subscription, eg: 2008) Subscription_ID (Can have multiple subscriptions in the past, so multiple ...
user avatar
0 votes
1 answer
33 views

Query to retrieve portion of a date

I have date values that are stored in the following format: Thursday, December 19, 2019 12:00 AM I need to retrieve just the month and day however I have been unsuccesful as I keep getting the time as ...
user avatar
  • 3
0 votes
0 answers
28 views

Difference between dates from two rows in one

I am trying to figure out how to compare two dates in SFMC. I need to see the difference between the first email sent from the same journey activity and the last one in the same row. The result could ...
user avatar
-1 votes
1 answer
38 views

Getting error message "Violation of PRIMARY KEY constraint. Cannot insert duplicate key "

Getting error while running this query in automation, customer key is set to be the Primary key. Below is the code : Select A.ContactKey, A.AccountId , A.ContactId , A.CreatedById , A.Email, A....
user avatar
  • 1
-2 votes
1 answer
35 views

How to retrieve the last 15 mins SMSSubscriptionLog records based on OptInDate using SQL Query?

I have a scenario that i need to retrieve the last 15 mins SMSSubscriptionLog (data view) created/updated records based on OptInDate using SQL Query. I will store the results in the created DE. If we ...
user avatar
  • 3
1 vote
1 answer
76 views

How can I combine the _SMSMessagetracking data view with another DE without having duplicates in Salesforce Markteting Cloud?

Great feedback. How ever, I still don't get the result I want. This is what I have with the outcome and what I expect: SMS Tracking view I created the SMS tracking DE with all the fields from the SMS ...
user avatar
  • 103
0 votes
1 answer
58 views

SFMC-Query the All Subscribers count on a specific date?

I need a query to count the number of active subscribers on specific dates, January 31, February 28, etc. throughout the year into a data extension. I know this will be using the _subscribers data ...
user avatar
  • 1
0 votes
1 answer
77 views

Select Distinct only one column, when there are multiple columns

I have the following query in SFMC: SELECT b.SubscriberKey, b.BounceType, b.BounceCategory, b.EventDate, s.EmailAddress, b.JobID FROM _bounce b LEFT JOIN _Subscribers s ON b....
user avatar
0 votes
1 answer
42 views

SQL Does Not Apply In Automation

I want to know the behavior that when you apply the changes in the sql outside the Automation, It will be applied automatically in Automation? Ex. Automation[A] has sql activities. I did want to stop ...
user avatar
  • 5
0 votes
1 answer
45 views

Records duplicated on SQL Join (Marketing Cloud)

I'm trying to return the results of subscribers who have been sent emails, when they've been sent, if they've opened/clicked and whether or not is house bounced/they've unsubscribed. I'm using the ...
user avatar
0 votes
1 answer
45 views

Query not executing

select top(200) * FROM ._Click C with (nolock) INNER JOIN CLM_Sendlog S with (nolock) on S.TriggeredSendID = C.TriggererSendDefinitionObjectID where C.TriggererSendDefinitionObjectID = '7e248b8b-...
user avatar
  • 1
0 votes
1 answer
23 views

Marketing Cloud SQL LinkName starts with instead of contains

I'm trying to use a query to identify LinkNames that start with a specific name, (Example_), but as a failsafe I don't want them included if the Example_ falls anywhere in the string other than the ...
user avatar
0 votes
2 answers
47 views

Equally Split a DE by segment

I have a DE with about 4m records. I am looking to do a 50/50 split for testing but by a specific column. So within that DE, every record has a "Segment". So there might be 500k customers, ...
user avatar
1 vote
1 answer
35 views

Unable to Retrieve Unmatched Sends from Source DE when Joining with the _Sent Data View

I'm having a difficult time joining data views to get the desired result of showing from a custom query that when using a LEFT JOIN I would expect to show all records for all 80466 seen in the Source ...
user avatar
0 votes
1 answer
89 views

Count the number of contacts in each Business Unit

I have 2 BUs in the Marketing Cloud and each has its own contacts. My issue is that I want to retrieve the number of contacts in each BUs (no need to be exact, just an appropriate number would be ...
user avatar
0 votes
2 answers
49 views

Why does selecting from _Sent and LEFT JOIN _Job return null values from _Job

When I select from _Sent and LEFT JOIN _Job it returns null values for fields selected from _Job. I'm querying from the Parent BU so this shouldn't be the BU specific issue. SELECT s.SubscriberKey, j....
user avatar
  • 21
0 votes
0 answers
94 views

How to get unique clicks, clicks, unique opens, opens with one SQL query?

I'm using the following (and very handy) consolidation query to pull out sends, opens, clicks, bounces and unsubscribes grouped by journey name etc.: SELECT jou.JourneyName, jou.VersionNumber, j....
user avatar
0 votes
0 answers
23 views

Split throught % of Open Rate

I have a journey where I send a 1st email and after that email I have to evaluate the opening % of that 1st email. In one way I will send another email to everyone who has an open rate > 20% and ...
user avatar
  • 1
0 votes
0 answers
67 views

Export results from Query Studio in excel file or SFTP

I am trying to export the results I get in Query Studio to Excel files without importing them into a data extension. Is there a way to do so?
user avatar
0 votes
1 answer
92 views

SQL Query Timeout Help SFMC

I have the following query that is consistently timing out for the last few weeks. It had ran perfectly fine since 2019, previously. I know the query needs to be optimized, but I don't see anything ...
user avatar
0 votes
1 answer
125 views

SQL query to update field only if it is empty

I need to run a query which updates a target DE with some rows from another. There is a field called 'origin' which shows on the target data extension where the data came from. I appreciate this may ...
user avatar
  • 792
0 votes
2 answers
49 views

How can I DISTINCT users with MAX recent date but different data between them in Marketing Cloud

I need to select the most recent purchase of each client in the same query. My problem: when there is a customer with several purchases and the CODE field is different, it shows me all of them. This ...
user avatar
1 vote
1 answer
43 views

How to use an SQL resultset as the name of a Data Extension in a query

I have two Data Extensions one with a field that contains the name of another data extension SetUp DE ID Audience 01 bigAudience 02 smallAudience smallAudience DE PersID 00001 00002 I would ...
user avatar
  • 13
0 votes
1 answer
38 views

Re-running query that dedupes a column with ROW_NUMBER returning increasingly more records

I've been banging my head against this problem for quite some time. I have a query that is automated to run twice a day and selects order records that fit a certain criteria. It puts them in a data ...
user avatar
  • 1
0 votes
2 answers
54 views

SQL: deduplicate multiple rows by field 1 while keeping multiple row values for field 2 [closed]

I'm trying to deduplicate the following dataset so it can be used in a MC journey (Customer is currently showing multiple rows based on Contact ID). I want it so that there is only 1 row for each ...
user avatar
0 votes
1 answer
51 views

SQL, retrieve one line with Max rule

I want to be able to have in the Data extension the information of the opportunity, the account, the opportunity product and the product information. I need to establish a rule to select the ...
user avatar
0 votes
2 answers
43 views

SQL query to add incrementing number for each group [closed]

I would like to create a SQL query in automation studio that adds an incrementing number to all animals of the same type. This is my current data extension: UID Animal 1 Cow 2 Pig 3 Cow 4 Bird 5 ...
user avatar
  • 5
0 votes
1 answer
50 views

SQL help needed - Marketing cloud

have a query that I would appreciate some help on, I'm not too sure why its not working. Select ContactID , CASE WHEN Special_credit = '0' AND Credit_Check_Status__c = 'Approve' THEN 'SENT' ...
user avatar
0 votes
0 answers
24 views

Salesforce marketing cloud date and time [duplicate]

In salesforce marketing cloud UI (Tracking) , I see a delivered date as 8/12/2021 9.04. In the sql on Jobs data view , i get delivered date 12/8/2021 2:04:00 AM I know , both dates are same and ...
user avatar
0 votes
2 answers
48 views

Marketing Cloud - How to group DE rows with repeated values

I have a DE (DataExtension) for members, and another for members' children, which are also members but linked to the parent in question. I would like to be able to group up sends, so I don't ...
user avatar
0 votes
0 answers
38 views

Issue passing UTF-8 encoded data to data extension through SQL

I have a problem with the data displayed in one field and got specific characters : "Espace Commercial René Coty" rather than "Espace Commercial René Coty" I tried the formulee ...
user avatar
-2 votes
1 answer
40 views

How to parameterize a Marketing Cloud SQL query?

I have a single, long and complex query, nested and all, using various UNIONs and EXCEPTs. In several WHERE clauses fields are compared to literal strings and some of these literal strings are used in ...
user avatar

1
2 3 4 5
16