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
0
votes
1answer
28 views

Marketing Cloud replace function in SQL

In my scenario I have to replace the new line character in a record for a particular column to some other character. I have tried below query but got the error. select CAST(REPLACE(REPLACE(CAST(a.[...
0
votes
1answer
66 views

SQL Query : Using JOINS with CASE Statement [closed]

I've 3 DE MASTER DE with fields SubscriberKey(PK), donottrack, Createddate, Modifieddate Staging DE with field CustomerID(PK), Createddate Reference DE with field donottrack default value false. ...
1
vote
1answer
40 views

MC query dedupes records automatically

I've noticed some quite odd behavior from running queries in automation studio. Below is the query in question: SELECT C.id , CM.id FROM ent.CampignMember_Salesforce CM JOIN ent.Contact_Salesforce C ...
0
votes
1answer
42 views

How to get external key (customerkey) from an email trigger?

I want to get all external keys from email triggers in a journey for stopping/publishing/restarting. So far, I can get their JourneyActivityObjectID (TriggeredSendDefinitionObjectID) through the SQL ...
0
votes
1answer
27 views

SQL: Is GETDATE() a deterministic function or not? [closed]

SFMC Limitation: We can't use variables Self Limitation: No usage of SSJS I use SQL Query Activity to get the previous day's records from Midnight to Midnight, just like a Tracking Extract might. e.g. ...
0
votes
1answer
13 views

Possible Bug - Business Unit Subscriber Status Data View has no records?

The BusinessUnitUnsubscribes Data View which is meant to contain the unsubscribe reason has no records. SELECT COUNT(*) AS count FROM [_BusinessUnitUnsubscribes] AS bu Returns 0. Is this data ...
0
votes
0answers
11 views

all subscribers per business unit - SQL code data views

I'm trying to get the number of subscribers per business unit using this code so I was wondering is this the right way to do it (using data views in Query studio): SELECT count(a.SubscriberKey) as ...
0
votes
1answer
37 views

New SQL activity for No Clicks or Opens

I have this task to build a new query activity for subscribers who didn't open or clicked in any of the newsletters our company have been sent. The following SQL query is what I've written down, and ...
0
votes
1answer
21 views

Marketing Cloud Report: Count Email Send by Email Name, Month over Month - 1 Years Data

I would like a report that counts the number of emails sent by month, by "email name". Is there a tool that offers these metrics? I know this can be accomplished using SQL joining _sent ...
1
vote
1answer
48 views

How to extract Device opened data in SFMC using SQL Query?

Currently on a daily basis we store email open/clicks etc. data in a data extension in SFMC and then export it to our data warehouse. Example : SELECT O.JobID, O.SubscriberID, O.EventDate, O.IsUnique, ...
0
votes
1answer
32 views

In an automation, I want to query a number from one table and then use it as a parameter for a select top statement in another query

I hope I'm explaining this well enough. I have table/DE a, where column a is strings and column b is numbers. I want to query table/DE a and select a record that has a specific number, and then use ...
-2
votes
1answer
35 views

How can I consume a SQL database to create / update records in salesforce using apex? [closed]

I would like to consult you for something, do you know if it is possible that a sql database can be consumed from salesforce? What happens is that account records are loaded into the SQL database and ...
1
vote
1answer
83 views

Find External Key of Data Extension from JourneyID [closed]

Can we find the External Key of Data Extension related to the Journey Builder using JourneyID? Is this possible to be done by SSJS or SQL?
0
votes
1answer
29 views

Compare today's date with date in DE [closed]

I'd like to update my original question, I've figured out how to convert getDate() to today's date. But is there a way to change the field from datetime to date when I do the query? For example: ...
0
votes
1answer
24 views

SQL Query using where with multi option [closed]

im trying to write down a query to filter data in SFMC but it seems there is something wrong please I need help query : Select Email, phone, LastName, FirstName From [sendable] C Where phone = ('...
0
votes
1answer
40 views

Left Exclusion Join when data extension has composite primary key [closed]

I have a query on writing left exclusion joins when the data extensions has the composite primary key. I have below two data extensions DE1 ContactID AccountID attribute sksksksk 111111 six DE 2 ...
0
votes
1answer
34 views

SQL Joining Multiple Tables into a Data Extension [closed]

I have two Data Extensions with fields "MID" and "Total". And a third DataExtension with MID and Name. I want to make a NEW DataExtension that has these three tables joined into ...
0
votes
1answer
59 views

Query Activity - Merging Data Extensions with Different Column Names [closed]

I have a Data Extension called “Merged Data Extension Newsletter” that has fields called “FirstName”, “LastName”, “Email”, and “Id” I am using ID as the Primary Key. I have another Data Extension ...
0
votes
1answer
29 views

DATE function to get past 60 days of data

I am running a query to get data from the past 60 days and BEYOND. So basically I am trying to capture anyone whose installdate is Today - 60 days and beyond. And I should be getting data whose ...
0
votes
1answer
62 views

SQL Query to find Duplicate email under different ID [closed]

I am trying to run a SQL to get a list of Distinct email ID. For example if the John Does john@somebody.com Jane Doe john@somebody.com Alice J mary@abc.com I want my output to have only Jane Doe ...
0
votes
1answer
35 views

I cannot "add" additional column to the OPENED Email Query

I'm trying to pull contacts who opened specific email. I'm beginner so I'm using the query i found in documentation that works but on top of that query I would like to add additional column. Here's ...
0
votes
1answer
44 views

SQL Query activity results with impossible records number

I have an issue with SQL Query activity / Query Studio where I am getting an incorrect number of records when I am trying to find the difference of records between 2 Data Extensions. I need to find ...
0
votes
1answer
52 views

Cross Apply in SFMC SQL Query Activity

SELECT e.cust_id AS customer_id, e.event_id AS event_id, max(t.updatedON) as recent_timestamp FROM event_table AS e INNER JOIN car AS c ON e.cust_id = c.cust_id and e.event_id = c.event_id ...
1
vote
1answer
39 views

Creating a log of all subscribers' most recent email. It's missing some people. SQL help?

For about 18 months I've been running an automation to keep a list of all subscribers and the date of their most recent email. I have two queries and two data extensions. Number 1 is: SELECT ...
0
votes
1answer
40 views

Multiple SQL statements in one Activity [closed]

We have to check in our transaction database to see if the product is bought. We get the data like: DB1 key product_1 Product_2 product_3 111 123456789 789456456 456789132 123 487462538 487454521 ...
1
vote
2answers
46 views

Exception occurred during [UpdateQueryDefinition]

I can't figure out why this isn't working. I'm trying to update a QueryDefinition (SQL Query) using this Soap Envelope. <Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/" xmlns:...
0
votes
2answers
84 views

Marketing Cloud SQL not updating field in data extension

I have a synchronized data extension that SQL queries Lead data into another data extension that is being used in a Journey. The Journey sends out an initial email based on the status in the SQL query....
0
votes
1answer
38 views

Last 24 hours query not working in SFMC Automation Studio

The query we've been using for another use case for a few months, is not working on the current automation in which we want to send a journey email to customers who registered on our website in the ...
0
votes
0answers
48 views

Marketing Cloud Reports on Campaign based on the bounce rate

I'm looking for different possibilities of creating report in MC for a campaign based on the Bounce rate%. ex : I want to setup a report for xyz campaign where bounce rate is more than 5%. I know it'...
1
vote
1answer
31 views

SFMC Dynamic Timezone Sends

Timezones are a concept I have a hard time wrapping my head around. I'm trying to build a dynamic timezone program that runs an adjusted sendtime on a daily basis. Everyone should be receiving an ...
0
votes
0answers
51 views

Violation of PRIMARY KEY constraint. Due to one of the DE's not including a subscriber key [duplicate]

SELECT ob.SubscriberKey, ob.EmailAddress, ob.fname, ob.Country, ob.Language, ob.fin_no, dw.username, dw.password, fp.FirstOrderDate FROM FP_Onboarding_Root AS ob LEFT JOIN Active_FP_JP AS fp ON ...
0
votes
0answers
39 views

Automation Studio - Violation of primary key constraint, Cannot insert duplicate key

I have an SQL query running in automation studio on a daily basis. It takes data from data extension A and puts it in data extension B. It functioned without any errors for 4 months, but it started to ...
0
votes
1answer
45 views

SFMC SQL Query not ordering decimal values correctly [closed]

I'm trying to order a record so that the latest date, and the highest value populate first and then decrease in a descending order. This is what I've written: SELECT contact__c, ...
0
votes
1answer
54 views

Automation SQL - get random value for each record [closed]

I need to populate some Data Extension using another Data Extension that contains all the accounts data. I want to attach a new column and the value for that column should be randomized from another ...
2
votes
0answers
89 views

How to identify a SMS subscriber in mobile connect by a record in Sales Cloud?

As I understood, a subscriber will be added to mobile connect once the SMS was sent to the mobile number of that SFDC record. I tried to query the data to have a better understanding SELECT ...
0
votes
0answers
50 views

Query for all subscribers AddMethod, DateModified, DateCreated for mismatched emails with subscriber key in SFMC?

I'm going to start by saying this forum has been nothing but helpful and appreciate everyone taking the time to respond to others. This is my first post so I apologize if this is not following the ...
0
votes
2answers
41 views

SOQL Query to extract certain attachments fails

I want to export attachments of those Owners who belong to a certain Organisation. I tried: SELECT BodyLength FROM Attachment WHERE OwnerId.Organisation_Name__r = 'ExampleCompanyName', but got the ...
1
vote
1answer
45 views

Unsubscribes per business unit

Good day to everyone. Can somebody explain reasons why query for __BusinessUnitUnsubscribes returns 0 rows? Query looks like this SELECT BusinessUnitID businessUnitMID ,SubscriberKey ,SubscriberId ...
0
votes
1answer
45 views

Query Preference Attributes

Does anyone has any idea on how to query the Preference Attributes? I can see them in All Subscribers, but I can't find a way to query them. I tried doing a query activity on EnterpriseAttribute Data ...
0
votes
1answer
47 views

Update or Overwrite in SQL Automation when using Contact Data Attribute Group in Journey? [closed]

I have a Journey using Attribute Group / Contact Data. ContactKey:1:N The Journey Entry data extension (:1) in the Attribute Group, has a Today minus -2 days filtering in the SQL query in Automation ...
1
vote
1answer
87 views

Query with Union all and order by

I have this query to merge two DE in SFMC and I need to merge also the email duplicates inside. I have a syntax error near 'x' that I cannot find. Select top 1 with ties x.Email from ( Select a....
0
votes
0answers
55 views

Can NOT query a subscriber by its subscriber key

I haven't queried data from the _Subscribers data view before and faced with that issue. Please correct me if I did something in a wrong way. At first, I created a temporary data extension with these ...
0
votes
1answer
101 views

Querying data view (Open) and grouping by date in SFMC

I am trying to get the number of opens for each day (Example: April 15, April 16, April 17 and so on....) Date Opens Apr 15 17500 Apr 16 5000 Apr 17 200 I am using the below query, but not ...
1
vote
2answers
23 views

How to set default time in DE date field time using SQL in marketing cloud

whenever a record is created how to set the time in the Date field time value to 8AM by default. SELECT CustomerID, CreatedDate, DATEADD(Day, 1, CreatedDate) As NextDate from TestCustomers instead ...
1
vote
1answer
51 views

SQL LIKE statement results in unexpected behavior when field is delimited

I'll say that using LIKE or NOT LIKE works great when splitting out emails. I usally do '%@%', but something strange is going on here... I have a field and it has delimited values for example: Do not ...
0
votes
1answer
50 views

Query running hourly failing with primary key violation intermittently

I have a SQL query that is trying to pull three records and insert them into one. On table A, affiliation ID is the primary key, and a contact can have more than one affiliation. On table B, Contact ...
0
votes
1answer
40 views

SQL Query Error: Record Type ID' is a required for the Target Data Extension. Ensure this field is specified

SELECT c.AccountId as SubscriberID , c.DoNotCall , c.Email , c.FirstName as First_Name , c.LastName as Last_Name , c.HasOptedOutOfEmail as Email_Opt_Out , c.Phone , a.id , a.RecordTypeId as ...
0
votes
1answer
63 views

SQL query to get Open rate by domain

I'm trying to build an SQL query to get the Open Rate Percentage by domain for an email send through Journey build or scheduled send. Below is the query I have trying to figure out, but it's giving me ...
0
votes
1answer
44 views

Invalid column name in row_number() function

Why am I getting this error? An error occurred while checking the query syntax. Errors: Invalid column name 'Id'. SELECT (L.Id) as 'SubscriberKey', L.ID__c as 'ID_C', L.Email as '...
1
vote
1answer
48 views

Calculate difference between 2

I have a DE with the number of customers, like this: Entry_Date Customer_Total New_Customers 05/26/2021 5 05/27/2021 10 5 05/28/2021 21 11 I need to calculate the value for new customers daily, ...

1
2 3 4 5
15