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
31 views

How can i know the list of emails who has clicked an unsubscribe link? [closed]

Im trying to get a list of emails with their subscriberkey from the data views _Open, _subscribers and _Click.
0
votes
0answers
23 views

Automation Studio to fetch 'subscriber status' = 'active' from All subscribers and store it in a new Data Extension? [duplicate]

I need to create a automation, I want to fetch 'All Subscribers'(from email studio) into DE Filter this DE to show only Subscriber_Status=active My goal : Create a DE with customers that have ...
0
votes
0answers
14 views

Finding SubscriberKey Who Did Not Open Across Two JobIDs

I am trying to create a SQL query that would return results for contacts that were sent two emails and didn’t open both. The count should be around 9,000 but I’m getting a much higher number. I think ...
0
votes
0answers
13 views

Querying _job data view is resulting DeliveredTime = Null

I have a journey builder send in a child BU without using any publication list. In the journey builder UI and email tracking, I can see that sends were being delivered and opened. However, querying ...
0
votes
2answers
28 views

How to limit a query in Marketing cloud

I am trying to query a data extension which stores email to get back subscriber key, the DE stores email from a cloud page form so that I could redirect them to a profile center by making a custom ...
0
votes
0answers
25 views

Selecting 59m records daily and overwriting a target DE times out [duplicate]

Very simple issue - but, as we all probably know, SQL timeouts in SFMC are set to 30 minutes. However, I have a query here that overwrites the target data extension: select ContactId, FirstName, ...
0
votes
0answers
11 views

What's the best way to remove ALL non-printable ASCII characters within a SQL query? [closed]

I'm curious if anyone has a simple + scalable way to remove all non-printable ASCII characters from a query? We're trying to extract dataviews to an Oracle DB, but keep running into issues where these ...
0
votes
1answer
41 views

Date literals not working in Salesforce SQL query

I have the following query: SELECT First_name, Last_name, Email, Creation_date FROM Random_database WHERE Creation_date = YESTERDAY I am trying to pull out yesterday's records. But what I get as a ...
0
votes
2answers
40 views

Issues with validating case statement in SQL [closed]

I've created a query that pulls all data from one Data Extension (DE A) and moves it to another (DE B), renaming the fields as it goes. Now I'm adding a CASE statement to put a True/False value into a ...
0
votes
1answer
33 views

Automation Timeout

I'm facing an intermittent issue with one automation. A month ago I received a message from SFMC reporting a timeout error in one Automation, but then I re-run the automation the error doesn't show ...
0
votes
0answers
25 views

SQL - GETDATE() not working

I have two queries for identifying customers for promotion. The first target is composed of customers who did their last purchase more than 30 days from the current date and the WHERE clause works ...
0
votes
1answer
31 views

In Marketing Cloud Is it possible to select all rows from a data extension where a field value is unique - ie an ID field

I have a DE which has a number of rows of data (with a number of columns) ID Name date email 1 ABC 1234 @gmail 1 ABC 5678 @gmail 2 DEF 9101 @yahoo 3 GHI ...
0
votes
1answer
36 views

Clearing Records in a Data Extension using SQL

trying to clear the records in a DE using a query in Automation Studio but it's not clearing the data. Am I doing this wrong? SELECT Email ,[Activity 18 Digit ID] ,[Hidden Contact 18 Digit ID] ...
0
votes
1answer
18 views

How treat columns with colons in SQL

What is the correct syntax in SQL so that it accepts colons in my column names? I'm trying to query a Data Extension that was created from a Journey's Salesforce Data Entry source. Consequently, the ...
0
votes
1answer
36 views

Violation of Primary Key while using DISTINCT

Im trying to update a Data Extension with data from another Data Extension, very simple, you'd think. The target Data Extension has SubscriberKey as Primary Key and the source Data Extension doesn't ...
0
votes
0answers
28 views

Rownumber() is skipping numbers in query results

I'm using a query to collect records from 5 tables, exclude records from a 6th table, and then give the results an incremental number using row_number(). But when I look through the results, some ...
0
votes
1answer
29 views

Creating SQL to join 2 DE [closed]

I have a requirement for joining the 2 DE based on the below condition Where Billed_Item_Status_DT = 3 days prior And Sales_Brand_CD = '520' I've created the query but still not sure I'm right for ...
0
votes
1answer
40 views

Violation of PRIMARY KEY constraint. Cannot insert duplicate key (email primary key)

I have this SQL query and I cannot perform it due to violation of primary key. The primary of the source data extensions are the following : Account : Email Opportunity : OpportunityId ...
0
votes
1answer
24 views

Update one Data Extensions with another using SQL Query

Morning folks, I'm a newbie at MC SQL. Need help wrapping my brain around the syntax of a SQL statement. I have a Journey Builder (JB) Data Extension (DE) where I need the attendance field to be ...
0
votes
0answers
17 views

SQLCipher pod installation issue

Hi I am implementing Salesforce SDK while installing $pod install in XCode project I am getting error. Can someone look in to the issue and help me in resolving it. $pod install Analyzing dependencies ...
0
votes
1answer
36 views

GROUP BY in Marketing Cloud always to get unique values

We have a data extension to which the data is being imported from another system. Sometimes, an Id that's used as SubscriberKey for sending emails appears twice (we are using another primaryKey in ...
1
vote
2answers
97 views

How to Update a Data Extension through another Data Extension?

Hello Salesforce Community, I'm new in Programming (around 6 months with JavaScript and Java) and even more rookie in Marketing Cloud (going to my second week) and I'm exploring a use case for a ...
0
votes
1answer
68 views

Optimizing SQL for large volumes

We're working with somewhat large sets of data, around 15M - 35M records per DE, each row with 150+ attributes. What are some ways to optimize SQL queries when handling many records? For an exaple, ...
-1
votes
1answer
62 views

SQL Activity Primary Key Violation

This is the error i get: Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'WEALTH_BOUNCE'. The duplicate key value is (...
0
votes
1answer
56 views

Marketing Cloud SQL - Invalid Column name “Opt”

I receive the following error message for my SQL statement and I cannot find the error: "An error occurred while checking the query syntax. Errors: Invalid column name 'Opt' This was a quite ...
0
votes
1answer
26 views

Query Activity - complicated Date requirement [closed]

In my query activity WHERE clause I want to check the value of a CreatedDate. If the current day is Monday I want to bring in records where the CreatedDate is on the previous three days (preceding ...
0
votes
1answer
19 views

Pull data from sql to salesforce

I am looking to generate reports and dashboards on Salesforce and the data reside in my SQL server. How do pull data from SQL to Salesforce in order to create reports & dashboards in salesforce
0
votes
1answer
37 views

Query Unsubscribes for Child BU

I would like to get the # of unsubscribes per month for a specific child BU (1) I queried _Unsubscribe where EventDate is within the desired period (2) I queried _ListSubscribers where ...
0
votes
0answers
21 views

Split a string on delimiter with SQL in SFMC [duplicate]

I've got a table of 8 million+ records in SFMC with one column called "AllData" and records that look like this: D||John Smith|EML|johnsmith@gmail.com|Y|2014/10/22 10:15:08.000000| I'm not ...
0
votes
1answer
29 views

I want to copy over another field but remove data in SFMC SQL [closed]

I have two columns, one titled "Account_Number" and the other titled "Customer_Number". There are the same, except Customer_Number has "C" in front of it. If I have all ...
0
votes
1answer
38 views

SQL Activity Query - Combine Publication List to Data Extension

I have a Data Extension that contains a large amount of subscriber information for mails. As well, I have other subscribers that are stored in multiple different Publication Lists. I want to be able ...
0
votes
1answer
44 views

SQL Logic not Matching Drag and Drop Filter Results

We are using a Master DE that is a combination of two Objects and tying to filter from it to generate records to meet certain criteria. When we use the drag and drop filter from the Master DE, we don’...
0
votes
1answer
22 views

Extracting Email Addresses by Domains from Multiple Data Extensions?

I am trying to extract all Verizon owned email domains from existing data extensions in SFMC and placing these subscribers into a new DE that will contain only the subscribers who use a Verizon domain....
0
votes
0answers
35 views

How to query only subscribers that are in an active journey? [duplicate]

We want to get only subscribers that are on an active journey using SQL. Is that possible using data views, and without updating another status DE in every journey?
0
votes
1answer
39 views

Job id from DATAVIEW_SENT does not exists in DATAVIEW_JOB Marketing cloud

I need to report the number of email sent by subscriber. I use the bellow query to search all sent I get a job id for a mail sent via email studio but when I request the job by id in DATAVIEW_JOB ...
0
votes
1answer
26 views

UTF-8 in Marketing Cloud SQL Queries

Are there any chances to define in what encoding the query result will be? Let's say I have a standard query: select field1, field2, field3 from DataExtension How should the query look like if the ...
0
votes
1answer
53 views

Searching for column != values weirdly excludes NULL values in my queries

I have a query in Marketing Cloud which searches for records in my Contact_Salesforce DE. I have a condition where it excludes records if the values are within a certain range, what I've noticed is it ...
1
vote
1answer
79 views

Querying Mobile Push Demographics Table

I'm attempting to query our mobile push demographics table that's tied to the contact record to pull some Application Version data and I'm struggling in Query Studio. The name of the table is ...
0
votes
2answers
59 views

Transpose rows to columns using SQL in SFMC

I want to move data from rows to columns in a SFMC SQL Automation so that we can easily see all the bookings one customer has made, all in one row. We collect bookings on one unique reservation number ...
0
votes
1answer
20 views

Replace function - SQL - Data Extension

I have a data extension with a column titled 'Email Name'. My reporting I have it pipe (|) delimited into a .txt file. When the 'Email Name' column comes in, some values have spaces in the front for ...
1
vote
0answers
34 views

Marketing cloud System date vs Day light saving (Control via SQL)

I have a landing DE which have a field SystemDate and that is default to Current date. I need to write a SQL which pick data from landing DE and push into Y DE. Our SFMC stack is in Central time zone. ...
0
votes
1answer
45 views

Copy Synchronized Data Extension

I'm looking for a way to mirror a synchronised data extension into a local data extension. My SDE is currently counting +- 6mio records and 100 columns (and yes, we need them!). I feed the local DE ...
0
votes
2answers
34 views

Query Account Data Extension with 2 different keys [closed]

Just ran into a little situation, where it seems the query isn't doing what it's supposed to do. I believe it blocks on the joins and then doesn't proceed to the rest. I didn't get an error, but i'll ...
0
votes
0answers
8 views

How to store Opened Date, Send Date, Clicked Date from _Open, _Click and _Send Data Views in a single column in Data Extension?

I have queried to store the sends, opens, clicks, delivered, bounce and unsubscribed event tracking using a single query. My additional requirement is to store all the Event dates of sends, opens, ...
0
votes
1answer
34 views

Removing surplus characters in a Phone Number column in Marketing Cloud using SQL [closed]

In Marketing Cloud, we receive phone numbers from our CRM in a following format: +3xx yyyyzzzz or 003xx yyyyzzzz. We would need to remove the extra '+' or '00' as well as the extra spacing inserted ...
0
votes
2answers
43 views

SFMC Query Error “An error occurred while checking the query syntax. Errors: Error while parsing query. Please check the syntax”

I am creating this SQL query in Automation Studio. This query gives me an error: An error occurred while checking the query syntax. Errors: Error while parsing query. Please check the syntax When ...
0
votes
1answer
14 views

SOQL Query for Account - Junction - Account

I need to join Account (type = person account) --> Junction Object --> Account (type = business account) We have person accounts enabled on our org. I can easily write what I need in SQL, but ...
-1
votes
3answers
87 views

error message on sql: “Violation of PRIMARY KEY constraint. Cannot insert duplicate key.”

I'm getting this error for the SQL below: Violation of PRIMARY KEY constraint. Cannot insert duplicate key. Select DISTINCT SubscriberKey, Source From ( Select Email, ...
0
votes
1answer
27 views

Marketing Cloud SQL Query - Removing Rows containing certain values

So I'm trying to query data extension Data123, and I want to grab all rows where Email_Address doesn't contain 'TEST'. I thought the proper syntax would be Select * from Data123 Where Email_Address ...
0
votes
2answers
53 views

joining two tables [closed]

I'm trying to join two tables(to get the OrderLines) and I'm geetting 0 records even if I manually checked if there is matching records and there is multiple records for each customerID that are ...

1
2 3 4 5
13