Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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)

0
votes
1answer
16 views

Query From Shared Data Extension

I have a shared Data Extensions across multiple Business Units. I need to run a query from it in a Child BU but every time I try to run I get "× An error occurred while checking the query syntax....
0
votes
1answer
11 views

SQL Query for a MarketingCloud campaign opens?

I'm trying to create an automation that looks back at a specific campaign and sets a flag on their profile if the user opened the email. This campaign would run every day and then talk back to ...
0
votes
0answers
13 views

Journey Builder Query

I am trying to use the query supported by Marketing Cloud that triggers Journey Builder Sends by Email across different versions, but the query doesn't seem to be working on my end. Here is the query:...
2
votes
1answer
25 views

SQL Query valid but Automation to populate Data Extension with engagement data is failing

Attempting to create a 'score' data extension for each email contact, to measure engagement levels with email sends. Background: Aware the contact score function is available on Pardot/CRM, however ...
0
votes
1answer
34 views

Possible to REPLACE/REPLACE and then CAST a DE value in a SQL query?

We're writing SQL for an Automation to find orders that qualify for a promo based on spend. The source DE has a TEXT field with a dollar sign $, the order value and some HTML <br /> - i.e., $...
-2
votes
0answers
31 views

Marketing Cloud Query [on hold]

I am a newbie in Salesforce marketing cloud. I need a query that will get the total journey daily. Display is the journey name, status, current day data and previous 7 days data. Here are my Sample ...
0
votes
0answers
28 views

Can't CAST TEXT field successfully into a DECIMAL (18,2) data type

Client is looking to send a coupon for any orders over $250. However, they are passing the order value as a TEXT field, not a DECIMAL. I'm trying to run the query by using CAST, but no luck. I've ...
2
votes
1answer
34 views

error thrown while trying to do a group by

I'm trying to write a query that gives me the name of users and the total number of ids assigned to them. Here I've Daily_Task__c a custom object with a lot of fields, here I'm trying to get the ...
0
votes
0answers
9 views

SQL Query for Attribute from All Subsribers

In our Marketing Cloud account we have a field against each subscriber that we call "SourceSystem". I have a requirement to delete all subscribers where the "SourceSystem" is equal to specific ...
0
votes
1answer
26 views

Compile member list of people who were sent to a specific Journey Builder activity

We have a number of decision splits in our journey. I am looking to build an audience list of everyone who exited after failing a specific decision split. I know each activity has a unique ID so ...
1
vote
1answer
21 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 ...
0
votes
1answer
33 views

Ampscript Lookup Product Recommendation Table with Product Images, URLs and Names

I have this table (Product_Recommendations) of recommended products (4) per email address. I want to add these products into my email, using ampscript lookup. I tried the following, but it was ...
0
votes
1answer
30 views

How to get the Exclusion List via SQL Query in Automation Studio

I need to exclude List to whom the particular email has been sent previously from Automation Studio via SQL Activity (Data views). select distinct s.SubscriberKey from _sent s ...
0
votes
3answers
45 views

Marketing Cloud SQL statement

I'm trying to run an exclusion SQL across a list I have. I have three separate brands in this list with customers that can sit across 2 or 3 of these. I want to remove customer and their opps ...
1
vote
0answers
41 views

SFMC SQL Query to retrieve contacts that have not clicked any email sent in the last 6 months?

Can someone validate the following query for me? I am trying to retrieve all subscribers that were sent an email in the last 6 months and have not clicked any? SELECT distinct s.SubscriberID ...
1
vote
0answers
25 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
42 views

SQL help on Data Views for _SENT table

In creating an audience for a Journey, I'm pulling everyone who already received one of our Welcome emails so they skip that step in the Journey. To do this, we've written the query below and it ...
0
votes
1answer
32 views

SQL Query Openers Fail

I am trying to run a query of all of our email openers that are older than 64.5. My SQL looks right but I keep getting the following "Automation failed due to system error." is this on my end or on ...
0
votes
1answer
37 views

SQL count not working as expected

I have a query that aims to pull counts for: total subscriber records, total for each subscriber status, total optins, total optins for each source, total unsubs, total unsubs for each source. I am ...
0
votes
1answer
26 views

Merge 2 Id fields in query to use ampscript to update a salesforce object

I would like to update a custom salesforce object when a marketing cloud email is sent. I have a transaction object and once the email is sent, I would like to update the description field to “...
0
votes
1answer
46 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....
0
votes
0answers
15 views

Issue with contact update in journey builder

I want to know below scenario are possible or not ? Scenario:I have multi select picklist in salesforce org which contains value A, B , C, D. Marketing Cloud and Sales Cloud already connected All ...
0
votes
0answers
24 views

Help with Automation studio Query - Primary Key Constraint

Getting the following error: Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'LIVE_ClearToClose'. The duplicate key value is (...
2
votes
1answer
81 views

Optimizing SQL queries

I've set up an automation that resends an altered version of a mail to all who recieved it the first time it was sent, but didn't open it. This is done in Automation Studio using 3 SQL Query and a ...
0
votes
1answer
23 views

How to find the number of emails sent to a single domain within this entire year by creating SQL Query for salesforce

I am using this below and it is not pulling the count into the data extension created once automation is complete. Only SubscriberKey and EmailAddress pulls in to the data extension, once automation ...
1
vote
1answer
26 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 ...
0
votes
2answers
39 views

Marketing Cloud SQL Query to determine no of null columns

I am trying to execute the below query in a query activity. The intent is to find how many emails a customer record have. The query is executing without errors but the No_Of_Email column is not ...
0
votes
2answers
28 views

Trying to run a sql activity thourgh a curl SOAP request - SoapAction incorrect?

If I manual trigger/run the activity the destination Data Extension is being filled correctly. However when try a SOAP request using curl, nothing happens... I've tried 2 different SoapAction values. ...
0
votes
1answer
53 views

Looking for a count of records using multiple where statements

I have an issue that I can't seem to write the correct sql for... I work for a railroad where we have three stations. I'm looking for the number of "long hauls" that a guest has taken - meaning they ...
0
votes
1answer
32 views

Using query to populate target data extension for resending automated mail

Every week we send out a mail on tuesdays. This mail is named so it always contains this: "TOBERESENT". Every saturday an automation sends out a mail that is an altered version of the tuesday mail. ...
0
votes
0answers
29 views

T-SQL Automation Studio activity by minutes period

I see there's no option to automate and run an sql activity in frequency of XY minutes... The shortest unit of time is the hour (hh). I know the T-SQL query supports the mi,n for minutes. But the ...
0
votes
0answers
41 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 ...
0
votes
0answers
19 views

SAQL group by with limit

Facing an issue with the limit keyword. I did group by Industry & Account type and want to see the Sum of amount for 5 accounts. If i set limit to 5 it is showing accounts < 5. What could be ...
0
votes
1answer
87 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 ...
0
votes
2answers
48 views

Error when trying to query All Subscribers

I'm trying to query all subscribers through an SQL query. I've copied a query which I am using in another instance of marketing cloud which works fine. However in the new instance the query activity ...
0
votes
0answers
37 views

Query on Suppression List Does not Return any Record

Ive tried the following code but it does not return any record. Select l.SubscriberKey as SubscriberKey, l.[Status] as Status, l.ListName as ListName FROM [_ListSubscribers] l WHERE l.ListName = '...
1
vote
1answer
28 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
133 views

How to write a SQL query with dynamic fields?

For example, I have a DE table(DE_A) with field a, b, c, d. The client may add e,f,g in the future due to their needs. My current SQL Query activity is : SELECT CONCAT("prefix", a) AS a, CONCAT(b, "...
0
votes
1answer
41 views

Can I pull DISTINCT customers with SQL based on most recent row record only?

So, I have a data extension where data is being saved. My data extension (CustomerSourceDE) looks something like this: Id (text) NULLABLE Email (Email) NULLABLE Customer_FirstName (Text) NULLABLE ...
1
vote
1answer
46 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 ...
2
votes
1answer
41 views

LinkName appears differently for different recipients

In our Journey, we are setting an ALIAS using AMPscript and are using a SQL to find clickers on that specific URL to create a Target DE for a follow-up/confirmation email. But there are different ...
0
votes
1answer
103 views

Query issue - Automation failed due to system error

I have a sql-query where i do not know my mistake therefore it would be nice if you could help. SELECT SUM(b.HardBounceCount) AS HardBounceCount, SUM(b.SoftBounceCount) AS SoftBounceCount, SUM(b....
1
vote
2answers
27 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 ...
0
votes
1answer
41 views

Recycle bin in marketing cloud / SOAP call active objects

I'm using a SOAP call to perform an SQL Query and I get this error: QueryDefinition was unable to queue this task. This query definition is not active My call : <RetrieveRequestMsg xmlns="http:...
2
votes
0answers
87 views

Joining Enterprise Attribute Data View and Business Unit List Subscribers Data View

I need to query subscriber profile attributes, which are only available at the parent Enterprise level EnterpriseAttribute Data View, together with the subscriber’s Status in the child Business Unit ...
1
vote
1answer
38 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
47 views

Send/Create an SQL request for Marketing Cloud with SOAP API

I'm working on a custom activity for JourneyBuilder in nodeJs and I need to make an SQL request like in automation studio to make a process easier. I found the package node-saleforce where you can do ...
0
votes
0answers
54 views

Possible to query to see if a subscriber has completed a Journey?

Is it possible to use SQL to find out if a Subscriber has completed a Journey? If not via SQL, does anyone know of another way? I looked at the Journey Data Views but they don't seem like they're ...
0
votes
1answer
31 views

Find Missing Date Ranges in SQL

Is there any way to Find Missing Date Ranges in SQL? Basically, I'm trying to find out how I can identify any missing days the last 3 months. I've already got a list of dates and volumes but unable ...
1
vote
1answer
37 views

Violation of Primary Key - SQL query

I'm copying a data view (_unsubscribe) to a standard data extension so I can view it and manipulate it for our preference center. It a simple query but I'm getting a violation of primary key error. ...