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
2answers
141 views

How to prevent a subscriber from entering multiple journeys at the same time in Journey Builder?

We have multiple journeys built using journey builder in our SFMC instance. We are trying to enforce a new business rule which should prevent the same subscriber from entering multiple journeys at the ...
0
votes
1answer
89 views

Delete records from a Data Extension created by Journey on the basis of date

I have a journey in which entry criteria is Salesforce Data. Whenever a contact enters into a journey, the journey puts its details into a DE(created by the journey itself). I want to write automation ...
0
votes
1answer
58 views

Filtering Opens and non-Opens in Data View SQL

We would like to get all opens data together with email sent/delivered data for the last 6 months. The goal is to separate openers from non-openers by filtering the resulting data extension. We are ...
5
votes
1answer
108 views

Error appending a Data Extension referenced in query

I have a query in a child business unit with an action type of 'Append.' This query contains a join, and it's targeting one of the Data Extensions referenced in the join. The query was originally ...
0
votes
1answer
73 views

Query to fill the empty column with the next available value from the Data Extension [duplicate]

I have a data extension with a customerID and 15 products assigned to them(screenshot attached) Some of the 15 products are missing the value and I would like to write a query so if Product1 is empty ...
4
votes
1answer
154 views

Marketing Cloud Use SQL Activity to Join Multiple Data Extensions

I've recently completed an IP warming process. Contacts were being added to a new Data Extension before each send to keep track of the the separate imports throughout the migration. Now I would like ...
0
votes
1answer
126 views

Is there a BASE64 Encoding Function using SQL in Automation Studio? [closed]

We'd like to BASE64 Encode our Email Addresses so we can send them to an external third-party (it's the third-party's requirement). We know how to do it using AMPscript, but not in SQL. Ultimately, ...
0
votes
0answers
56 views

SQL Query WHERE Clause in automation not working

I have an automation that fetches a file from the FTP and populates a Data Extension. The query is working fine if I just do select from in the automation. One of the columns in the Data Extension is ...
1
vote
1answer
27 views

Additional Fields Added to _Open Query

I am trying to get tracking information on people who did not open a specific email campaign. I was able to get it to work using the below query: Select distinct s.SubscriberKey, s.JobID, s.BatchID, ...
0
votes
1answer
89 views

SQL Query: looking for a particular email name and user it was sent to

i'm a newbie with SQL, and i wanted to pull out all subscribers that were sent by a particular email with open date send date and click date. @Bread2827 help me out with this query below but i can't ...
0
votes
1answer
115 views

Marketing Cloud: Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

Everyday we have an automation running to import new magento customers from an FTP file to our contact DataBase. Today, for the first time we had this error message. Query failed during execution. ...
0
votes
1answer
38 views

Querying all contacts from DE field in SFMC

Is there a way to query all contacts based on a data extension field? IE query everyone with the last name "Johnson" and the DE field is Last Name. Thanks in advance.
0
votes
1answer
77 views

Query DE with multiple Productid's to delete products that don't match another DE

I have this two Data Extensions and the ProductCode is the same as Product1, Product2, Product3, Product4...Product10 The problem is that there is some records of products in Product1, Product2......
0
votes
2answers
99 views

SQL query to find subscribers who were sent with the same Email Name

I've been using marketing cloud for almost a year now but not really exploring the SQL queries since i'm not really familiar with this. I'm also aware that my friend set up my account with lots of ...
1
vote
1answer
76 views

Query Open from Journey Builder

I tried to do the SQL for get who opened an email in journey builder. I use this SQL below, but no records populates my Data Extension. I tried to use whith and without the prefix 'ent' before the ...
1
vote
1answer
2k views

Using SOQL with ExactTarget, how to I access the “System Data View”?

Trying to fetch subscriber data from a specific list from ExactTarget. When querying the "System Data View", an error is returned saying "System Data View cannot be accessed" - which might be ...
1
vote
1answer
987 views

Query Retention Email based on Data Extension in ExactTarget

I need to create an automation in ExactTarget which would send Emails to inactive consumers "x" (e.g. 100 days) days after subscribers make it to the Data Extension. Inactive means: subscribers haven'...
2
votes
1answer
1k views

SQL Query EXCEPT not functioning as expected

I have 2 tables within SFMC both with SubscriberKey as the only field, which is the primary Key. There are SubscriberKeys that exist in both tables. Test1 has 1,700,000 records and Test 2 has 400,...
1
vote
2answers
157 views

Issue when combining WHERE statements using AND in SQL

I'm experiencing an odd error trying to combine multiple statements in my WHERE clause in SQL. For example, this statement runs fine and does exactly what i would expect: select abr.[Email Address] ...
0
votes
1answer
373 views

Day in Week function - SQL

I am trying to get the day of a week in a SOQL query but the marketingcloud just shows me this error message: "Error:'day_in_week' is not a recognized built-in function name." This is the query I ...
2
votes
1answer
774 views

Create a data extension with the number of clicks and opened emails

I'm working on a feature for marketing cloud and I need to create a Data Extension with those fields : NumberOfOpenMails,NumberOfClicks and all the other fields relative to the subscriber. I found ...
1
vote
1answer
476 views

Accessing the Tracking Data

Does anyone know how can I access / export the data that is in the Tracking section in Email Studio? I'm not interested in sending myself an Email Report. I'd like to get that data into a Data ...
0
votes
2answers
214 views

Marketing cloud Query Single field split into multiple fields

I have a data extension that has duplicate email addresses with each duplicate having a different rank associated with it. I need to dedupe the email addresses but keep the different ranks in added ...
0
votes
1answer
209 views

SQL Query is not retrieving data

I am trying to run below query, which successfully run but it does not retrieving any data. I am 100% confident it should retrieve the data as I have manually checked. What could be the reasons its ...
0
votes
2answers
211 views

marketing cloud soql filter by row number

I've a DE which has 2M records and would like to split it to 1.1M and 0.9M using SQL query. A query like below throws error. Any thoughts? select A, B, C, ROW_NUMBER() OVER (ORDER BY ...
0
votes
1answer
165 views

Oracle SQL to SFMC Date Issue

Running a query to send email subscribers a +X day email. FIRST_PROMO_SUBSCRIBE_DATE is coming from Oracle which they say is not a compatible format with Salesforce SQL so I have; select * from ...
0
votes
1answer
54 views

Check if date in field is after date in another field

I want to check if a field in one data extension is greater (after) a field in another data extension. I currently have op.[EventDate] > (acc.[Most_Recent_Auto_Loan_Application__c]+14) within ...
0
votes
0answers
45 views

How to form a Data Extension from other Data extensions

I have 3 DataExtensions DE1 with fields(name,EmployeeId(primarykey),Group(GroupA as value)) Eg: Name -test | Name -test2 employee - 123 | employee - 124 Group - GroupA | Group- ...
0
votes
1answer
131 views

Track Email Code snippet included in email, yet no opens are being recorded

I'm using the data view to query opens for the last 30 days. It's been working for the past 2 months. My devs just re-wrote our HTML based email with it's ampscript but the code is still there. I ...
0
votes
0answers
238 views

Marketing Cloud: Convert STRING to INTEGER(NUMBER) using SQL

We would like to do a SQL query on a master data extension from which there is a data column which was set as "Text" instead of "Number". We would like to make a conversion into the target DE from ...
1
vote
0answers
26 views

Working on setting order on the CTR highest on top and issues displaying data in DE

SELECT Distinct D.URL AS [Link],a.jobid AS [JobID],F.Emailsubject AS [Email Subject],a.Country as Country,a.consumerlanguage as consumerlanguage, CAST((SUM([Unique Clicks]) SUM([Sent]) * 100) AS ...
0
votes
1answer
47 views

SQL query contacts don't exist in another data extension

I have: [Data extension A] with CustomerID(PK) [Data extension B] with SubscriberKey(Nullable) which has the same value as CustomerID Attempting to get all the customers from [Data extension B] ...
2
votes
2answers
480 views

Querying DE for subscriber Status = Active not working?

We're trying to find the number of subscribers who are marked as "ACTIVE" in a Data Extension. Seems simple enough, right? Here's our SQL SELECT a.Email From [Newsletter List] a JOIN ...
1
vote
1answer
59 views

Query to compare branches of a journey after a percentage split

I ran a journey where I used a decision split to separate the audience into 4 branches and then sent each branch the same series of emails with varying wait times in between sends. Now I want to ...
0
votes
1answer
40 views

Getting sent data for a specific time frame using Query

I am trying to pull send data from some scheduled sends. I am using below query: SELECT s.SubscriberKey as Email_Address, s.EventDate as SendTime, s.JobID as JobID, d.VER as VER, d.CellMarket as ...
0
votes
1answer
69 views

Multiple SELECT in subquery

In Marketing Cloud, we would like to check for the existence of records from one table in multiple other tables based on the unique identifier - Customer_ID. However, I'm not sure whether I can ...
0
votes
1answer
35 views

Automated way to set SEND VOLUME by day?

We have a list of 100K subscribers and we want to send a maximum number of emails per day - but the max volume changes by specific day. For example we want to send more emails Tuesday through ...
0
votes
2answers
91 views

How to know total Technical and Unknow Bounce from Journey Builder E-mail's

I noticed that the report from Analytics Builder does not have this field. Is there a way to know this with SQL? Only the report "Journey Builder Email Send Summary" shows me is the Soft, Hard and ...
1
vote
1answer
95 views

Query data extension with multiple columns to find the products that don't exist in another data extension

I have this two Data Extensions and the ProductCode is the same as Product1, Product2, Product3, Product4...Product10 The problem is that there is some Products in Product1, Product2...Product10 Data ...
0
votes
3answers
95 views

Query did not opens in the past 180 days

I have the following query to find all subscribers that have not opened in the past 180 days: Target Data extension has: SUBSCRIBER RELATIONSHIP ContactKey relates to Subscribers on Subscriber Key ...
0
votes
1answer
68 views

Marketing Cloud - SQL Group By returning all rows

I am trying to create a query to return only the last date of a set of records. The process is: A task is created in Salesforce and triggers a journey. In the task, agents can choose to send an SMS ...
1
vote
2answers
179 views

Marketing Cloud SQL Case Function

I have an already existing SQL function running in Marketing Cloud and have been attempting to add on a CASE function to determine a column of "State". I have tried a couple different versions of this ...
1
vote
0answers
211 views

Getting something more than “Error while parsing query. Please check the syntax” in SQL Query Activity

I am creating a Query Activity in the Automation Studio. The query gives the following message in red: An error occurred while checking the query syntax. Errors: Error while parsing query. ...
1
vote
1answer
236 views

Replace semi-colon not allowed in SQL - alternatives?

Looking for solutions to my Replace() issue outlined here, I thought an alternative might be a replace within SQL when moving data to the deployment data extension. However, I've discovered that we ...
0
votes
1answer
962 views

Converting date/time to string in Marketing Cloud's SQL - string to date conversion error

We are trying to convert a string field to a date field using the CAST function in Marketing Cloud. Our goal is to use the newly converted date records to segment our subscribers based on the date of ...
0
votes
0answers
36 views

SSJS to bulk update SQL Activities

I have many SQL Query activities each of which i need to update the query text to a) join a data extension b) add to existing where clauses to exclude the joined DE where the unique field IS NOT NULL. ...
0
votes
1answer
53 views

Select Records from Different Sources

I am trying to grab email address from different sources for selected Account IDs. I have all email address in Contact table those are grabbed from Account and Opportunity table. So task is first I ...
0
votes
0answers
15 views

Excluding test sends from Email Open Data View query [duplicate]

we set up sth. like a custom lead scoring in Marketing Cloud by using web webtracking and data views. We are gathering everyone who e.g. opens/clicks an email, visits a specific page on the website ...
0
votes
1answer
60 views

Excluding parenthesis using where clause

How to exclude values, where values contains "(" paranthesis. For example I tried, Where Email not like '%(%' But I am keep getting an error. An error occurred while checking the query syntax. ...
3
votes
2answers
53 views

Sql Action “Run Once” fails to pull records

I'm trying to run a query that contains a few joins. It is populating a data extension. However, when I "Run Once", not only does it fill the extension with data, but there is no record in the ...

1 2 3
4
5
12