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
32 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
33 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
0answers
33 views

SQL Query help in Automation Studio [closed]

Trying to use automation studio SQL query to pull out unique values to different DE. What would be the simple script for this? Would Distinct work in this situation? E.g. SELECT Email,FirstName,...
0
votes
1answer
25 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
45 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:...
-1
votes
0answers
29 views

how to decrypt the email address using SQL query activity in marketing cloud [closed]

data coming from salesforce and email address is encrypted in synchronized DEs. Now I want to decrypt that email address which is there in sync DEs using the query and put the result in targeted DE. ...
0
votes
2answers
40 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
32 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
21 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
29 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
46 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
36 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
35 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
45 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
48 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
29 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
33 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
29 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
27 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
31 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
52 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
31 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
44 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
18 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
49 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
36 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
44 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
27 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
45 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, ...
0
votes
1answer
44 views

Send Log Join with _Job, _Click , _Open Need help

I am using a send log feature to store the sent email details -- like Name, EmailAddress, issueDate, CertificateNumber. The send log already has JobID, ListID, BatchID,S ubId, TriggeredSendID, ...
0
votes
1answer
30 views

Shorten zipcode in email using ampscript?

The data extension I'm working with has a zipcode field that, for some people, populates the 5-digit zipcode and for others it has the additional 4 digits. Is there a way with ampscript that I can ...
0
votes
1answer
49 views

How to update DE records (>10 years AND <1990)equal to NULL in SFMC Using SQL

How to null birthday records below 10 years and birthday records of those born before 1990 using SQL in Salesforce marketing cloud. Here in my DE Birthday is text field.
0
votes
2answers
122 views

SQL-Bounce data in Marketing Cloud

I am an not a pro when it comes to SQL.I am looking for help write a single query that gives me bounce data for my sends in last 90 days. When I try the below I hit an error "Unable to create ...
0
votes
1answer
45 views

SQL issue in populating a Data extension in Marketing Cloud

Could anyone help identify why this SQL won't populate to a data extension: Select l.Id , l.Email , l.FirstName , l.LastName , l.MC_Lead_Mobile_Phone__c as MobilePhone , l.Salutation , e....
1
vote
1answer
55 views

CHARINDEX(“(”,ColumnName)-1) errors

I'm trying to clean a column data by deleting what comes after the open parentheses: LEFT(columnName, CHARINDEX("(",columnName)-1) as columnName But I keep getting this error: An error ...
0
votes
1answer
34 views

Update a field if contacts don't exist in a data extension

I have: One Data Extension (Time_Throttling) with all contact details. Another DE (Time_Throttling_Transfer) that basically has some data from Time_Throttling. Now I have to update a boolean field to ...
0
votes
0answers
32 views

Select statement inside case statement to add rows to DE based on count

I am building an automated productselection. I want to add rows to a DE based on record count. So far Ive come up with underneath solution, but the syntax is not right: An error occurred while ...
0
votes
1answer
21 views

Tag emails and use the tags is SQL

Is there any way to tag/attribute a tag to an email (to categorize them for example) and use it in the SQL queries to get tracking information by email types (transactional, commercial, welcome pack, ...
0
votes
0answers
39 views

Error in Salesforce SQL query activity setup

I needed some help in writing this SQL query. I had it valid when I tried a different data extension as test. But when I tried to apply it to the needed DE. I got the below error message Error saving ...
0
votes
1answer
45 views

SQL birthday reminder, leap year in Marketing cloud [closed]

i'm trying to sort out a result set that sorted by upcoming birthday. This works perfectly until leap years comes into play. For example: Nov 14 1991 - 200Days left Nov 13 1988 - 200Days left Here ...
1
vote
1answer
119 views

How to use string_agg/group_concat in salesforce marketing cloud

I am trying to write something similar in query studio on SFMC. But keep getting string_agg is not recognized as a built-in function name. Please help. Also, what could be the alternative. Thanks ...
0
votes
0answers
34 views

Query All Results from a Journey in a single SQL

I am trying to query All the email results from a Journey in a single SQL but it doesn't work, so I separated a few steps to identify where is the issue. My final goal is to have a Data Extension with ...
0
votes
1answer
27 views

De-duplicating emails before send

I am trying to write a query to avoid duplicate emails being sent out. I am using contact ID as subscriber key, so multiple contacts can share the same email address across the DB. I do not want to be ...
1
vote
2answers
45 views

Is SubscriberKey the only field you can query from a MobileConnect list?

I need to query a MobileConnect List to delete some Contact records. I created a MobileConnect List and then used automation studio to query it into a DE. This worked fine BUT is SubscriberKey the ...
0
votes
1answer
37 views

Is TriggererSendDefinitionObjectID unique for Opens?

I´m having trouble to query Opened emails from a Journey accurately. I want to keep a Data Extension with all the openings from the journey <'Homem_Casado_30_55'>, version number=2. So this is ...
2
votes
1answer
40 views

SQL: How to get the right SUM of two different columns from two different data extensions into one new DE

I have the following tables: In the third table I want to combine these data with the SUM option in SQL so the total of clicks and Sends are in one field. Like this: I created the following query: ...
0
votes
2answers
62 views

Pushing data from one data extension to another

In Marketing Cloud, I have two data extensions. Both Data Extensions are being populated via entries in a journey. One runs off of the contact object and the other runs off the opportunity object. The ...
-4
votes
1answer
72 views

What is cast and convert in SFMC? [closed]

What is cast and convert in SFMC eg- SELECT Count(*) as Count, CAST(GETUTCDATE() as date) AS Day, GETUTCDATE() AS [Updated Date] FROM ENT.[Demo4 Contact__IQ] WHERE [Status Code] = 'Active'
0
votes
2answers
68 views

How would I retrieve data from one data extension and add it to another Data Extension? [closed]

SourceCodes are fields we assign to each email. They correspond to the email name. I need to retrieve the SourceCodes from Data Extension One (our source DE used for sending) and append them to the ...

1
2 3 4 5
15