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
1
vote
1answer
990 views

Violation of PRIMARY KEY constraint. Cannot insert duplicate key

Getting error on the query and is below. Email_Master primary key is 'Email Address' and thee target Data Extension primary key is also 'Email Address'. Can you help/advise what is the issue on the ...
1
vote
1answer
486 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 ...
1
vote
1answer
62 views

T-SQL get beginning of week date

I would like to query the Marketing Cloud _opens table to find any opens in the last 7 days but from the beginning of that given day. Using the following will get the last 7 days relative to the ...
1
vote
1answer
60 views

How do I update 1 DE based on data in 2nd DE?

I'm fairly new with SQL. I have 2 data extensions. DE1 is a list of email addresses and changes each day. DE2 is for counting how many times each address appears in DE1. So, my question is, how ...
1
vote
2answers
70 views

Importing data from multiple external tables to a single object

I am trying to use a production SQL Server database to send relevant data into Salesforce to generate reports. Some of the data I would like to use however is not consolidated into a single database ...
1
vote
1answer
734 views

Counting records by sql query and saving the all counts in one data extensions in different columns

So, I have two data extensions and I want to calculate count in 3rd data extension in each individual column. My DE 1 (included all Emails of customers) My DE 2 (included all Emails and Status of ...
1
vote
1answer
448 views

SQL Statement to insert a count by email and date

I have an issue which I'm trying to solve. We contact customers a number of times during an application. We don't count how many times we call them. I want to get an SQL statement which this by date. ...
1
vote
1answer
457 views

Click data view wont' save in data extension

So, I am seeing a very odd behaviour. When I run this query to dump all click data view data in a data extension, I see the record count going up...it goes into x millions and then suddenly the count ...
1
vote
1answer
2k views

How can use DISTINCT in SQL Query for extracting records from 1 Data Extension and dumping it in another Data Extension

I have 1st DataExtension names "Accessories" and 2nd as "TestAccessories". I want to dump all the unique records from Accessories to TestAccessories, My query is as follows: SELECT DISTINCT [Customer ...
1
vote
1answer
287 views

Using “Update” for Target Data Extension

I’m having trouble with Marketing Cloud’s “Update” option when I choose a target data extension. I want to update Tabel1 using data from the Table2. Whenever the rows for “Code” match the number 3, ...
1
vote
1answer
115 views

SQL To Determine Subscribers on a DE but NOT on a List

I"m trying to write a SQL to determine which subscribers are on Data Extension X but NOT on List ID Y. Here's what I've tried so far. select a.email_address , a.subscriberkey from ent.[listname] ...
1
vote
1answer
505 views

Query to extract Subscribers and what lists they are on

I need to extract Subscriber details and the List details they are on. My below query is working but how can I narrow it to see who has signed up on the website in 2017 and has never been sent an ...
1
vote
1answer
350 views

Determining Min and Max Values SQL Query Salesforce Marketing Cloud

I have a DE that lists of all ads on site. That data includes, maker, model and listing price. What I am trying to do is query out the following information: distinct model maker the min list price ...
1
vote
2answers
199 views

Query error in Marketing Cloud

The following query keeps throwing up error messages (different each time). Can anyone see something wrong with it? The query is designed to power an email sent to internal stakeholders letting them ...
1
vote
1answer
773 views

Aggregating Marketing Cloud Data Views

I have created a Data Extension (account) which contains an account_id and various boolean fields and email address, I want to create a field which stores the last opened date from _Opened table via ...
1
vote
1answer
525 views

System error when running a query with unions to join multiple data extensions

I am trying to pull the data from multiple data extensions to another DE and i am using union to achieve it.Also I have subscriber key as a primary key in the target data extension.However i get ...
1
vote
1answer
361 views

Error with Duplicate Primary key in Marketing Cloud SQL

We have multiple newsletters that generate leads for marketing emails. I'm trying to automate the process of consolidating those leads into one master data extension after they have been on a ...
1
vote
3answers
386 views

Writing a query to only match the first five digits of zip codes

We have a 336 five digit zip codes that we want to target and pull through using sql in Marketing Cloud. We tried a left join where we're matching up the zip code from the table of 336 to our customer ...
1
vote
1answer
81 views

SFMC Query: Never received/Never received an email that contains - single query

i am wanting to join two Quries together so they work as one rather than independently (both work as intended on their own) Query 1 The first one will select all subscribers who are customers and ...
1
vote
1answer
329 views

Using SQL script on data views for calculating stats

I have created an autoamtion that pulls the data from Data View and saved the records in a data extension (My_Sub_DE) using the following sql query. SELECT * FROM ent._Subscribers WITH (NOLOCK) Now, ...
1
vote
1answer
387 views

SQL query, simple LEFT JOIN substraction

I have a very simple query, but somehow it fails, im pretty sure its just a syntax error, but I can find the error... Its very simular to this example below.. I want every that ONLY exists in TableA ...
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
0answers
13 views

One automation with 2 SQLs triggering 2 separate journeys [closed]

We want to design an automation in Automation Studio that would comprise 2 SQLs. Both queries will populate data into separate data extensions, which we would like to use in 2 separate journeys as ...
1
vote
1answer
46 views

SQL - How to filter the max value

I am in the middle of creating an SQL in SFMC. I have three attributes in the source Data Extension: GlobalID, CategoryDescription MAX (yes, the name is weird, but it is not the point...). What I ...
1
vote
1answer
82 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
0answers
28 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 ...
1
vote
2answers
99 views

Query Data Extension with Left Join

I have one data extension that is about 9 million records (IGO 3 months simple). It has 2 columns, SKU and CustomerID. Both of the fields are NULL so there is duplicated CustomerID's and SKU's. I'm ...
1
vote
0answers
226 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
2answers
259 views

SQL Query to Identify All Contacts?

I am looking for a way to identify all contact within Marketing Cloud who don't have an email or sms activity within the past 2 years. I've already done a data extract to get that activity but I want ...
1
vote
1answer
39 views

Need to find engagement from existing Data Extension

I have a Data Extension in SFMC where I've captured customer purchase data, let's call it "cust_purch_12mo" and right now it only contains the customer email address and purchase date. I am trying to ...
1
vote
1answer
70 views

Send a Second email to subscribers that didnt open 1st email sent via automation studio

I need to create an automation to: 1- Send and Email (from a created email template) to subscribers and informations from a Data Extension DocCobranca. 2- After 2 days has passed I need to make a ...
1
vote
0answers
50 views

Pre-Birthday Selection

I´d like to send a pre-birthday mail 1 week before the birthday. Therefore I need the SQL statement. Right now it looks like that: SELECT * FROM MasterAbonnenten WHERE DAY([Birthday]) = DAY(...
1
vote
0answers
176 views

SSJS script activity not updating Query Definition as expected

I am trying to update a query definition in Marketing cloud using SSJS script which updates the query based on the datestamp. When I run the script, I don't get any error but it does not seem to be ...
1
vote
0answers
62 views

How do I order rows in columns using AutomationStudio SQL

I am performing a SQL query in Automation Studio and I want the following: Populate a data extension with ID of the Account_salesforce table with the ListName (Lists) bound to the ID of the first ...
1
vote
0answers
103 views

Data Views unique subscriber clicks

I've created a data views report for capturing clicks across my subscribers and I'm having issues with returning subscribers multiple times for different emails but with the same links and click event ...
1
vote
1answer
102 views

Using SQL to compare and update data extension

I need to use SQL in an automation to compare the data from the "TemporaryDE" with the Data from the "CompareDe". If the CompanyName value from the "TemporaryDE" is not present in the "CompareDe" - to ...
1
vote
0answers
697 views

SFMC: SQL Query on Dataview objects to calculate Journey's sends, opens, and clicks

I want to calculate the sends, opens, and clicks of all journeys. Expecting the records in Date Extension consists of records in the following sequence: JourneyActivityObjectID VersionID ActivityName ...
1
vote
3answers
171 views

SQL multiple WHERE clause problem

I have a SQL query that puts records from one DE to another DE if that record does not exists in the second DE. The base works fine for me and i have the following query: select t.Emailaddress from [...
1
vote
1answer
23 views

Pulling every email job an email address has ever received

I am looking to pull all the email jobs that a particular email address has received. The issue is our transactional unit uses email as a the subscriber key and our promotional unit uses unique ...
1
vote
1answer
77 views

SQL Won't Populate Salesforce Data Extension

I am trying to dump data from a Salesforce object into a Salesforce Data Extension. I am not getting an errors but the data refuses to populate. I can't figure out what I am doing wrong! I have tried ...
1
vote
1answer
62 views

Generic System Error when I use a Left Join

I'm creating an automation that uses a series of SQL queries to identify people who were potentially targeted for a dynamic content block in an email, determine if they were included in a job that ...
1
vote
1answer
177 views

Comparing dates in query activity with null values (in Marketing Cloud)

I query the _Opens data view which updates my SubscribersMain Data extension with the latest email open dates, I have noticed that the query blanks out the last_open_date when it shouldn't but cant ...
1
vote
1answer
145 views

Marketing Cloud Data Relationships - what kind of joins are created?

To be honest, I've never messed with Data Relationships before. I've always used SQL queries to segment my data. So, with Data Relationships, my assumption is that a SQL query is generated on the ...
1
vote
1answer
819 views

Query Journey Builder Sends

I want to run a query to pick up all subscribers who received a JB email in the last 7 days from a specficic Journey. I found this article but it is not filling the data extension! https://help....
1
vote
1answer
199 views

Select Minimum Date with SQL

I'm trying to select some records from a data extension where an email address appears more than once with a different date, using this query: SELECT emailAddress, MIN(scheduleDate) AS scheduleDate ...
1
vote
0answers
112 views

SQL Query is not retrieving PersonContactId

Marketing Cloud is connected to Service Cloud by the Connector: synchronized entities Accounts, Contacts (and 5 others). GOAL: I want to create a Data Extension in Marketing Cloud that ONLY ...
1
vote
1answer
72 views

Problems with Inner Join - Querys and DataTables - Marketing Cloud

I'm having an issue while working with several data tables, I ran the query and it's "working fine". But, the data is not being inputted on the other data extension that I need to be. The objective ...
1
vote
0answers
68 views

Find mail JobId with specific Campaign Code

Is it possible in Queries to find alle users who have opened an email with the Campaign Code of "Newsletter"... I Cant find the Campaigncode anywhere in the _dataview tables.. the closest is EmailName....
1
vote
1answer
310 views

how to get subscribers who did not open email in last 100 days?

We have all subscribers in 1 main data extensions "Audience_DE" within this data extension, we have one field, "Email_Sign_up" YES / NO All our emails are based on this field, we only send email if ...
1
vote
1answer
81 views

can i assign and send out coupon codes by query

i have a campaign in which coupon codes should be emailed to new customers who are entered in a data extensions. I already have a coupon data extension with code and IsClaim flag which is FALSE by ...

1 2 3
4
5
12