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

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)

0
votes
1answer
26 views

SQL Statement to merge two tables with multiple records

I'm trying to merge two objects using Automation Studio in Marketing Cloud so a customers opportunities/or no opportunities are all in the one table. I think this spreadsheet i created will make ...
0
votes
1answer
25 views

Inserting a value into a field in a Data Extension where records match on a key in Salesforce Marketing Cloud

Please excuse my ignorance, as this seems like a simple thing. I'm just new to to SQL and am having trouble working it out. I have a data extension of records with a blank Variable field. I have a ...
0
votes
0answers
18 views

Marketing cloud: Help with query to avoid duplicate records in data extension and getting the last opportunity unless a particular stage [on hold]

What I am trying to do, is to bring all the opportunity contacts roles for the last opportunity. However, if the account has an oportunity in a particular stage, then only use the opportunity with ...
1
vote
1answer
39 views

How to de-duplicate SQL query using ROW_NUMBER

I try to de-deduplicate my SQL Query but it gives this error: An error occurred while checking the query syntax. Errors: An error occurred while checking the query syntax. Errors: Invalid column ...
1
vote
1answer
23 views

Update Data Extension via WSProxy

I've built a CloudPage which runs SQL queries quickly without having to use automation studio. The SSJS that runs the query works perfectly, but I'm also trying to use this function to also update a ...
0
votes
2answers
44 views

Rewrite query to make it without subqueries

I have some custom objects displayed on schema. I need return vacancies which have last active interview at least week ago. I have some query: List<Vacancy__c> ids = new List<Vacancy__c>(...
0
votes
1answer
19 views

Multiply two fields if an ID is found in a separate table

Trying to create a Data Extension where we calculate the total spend in a category, if the category exists in a list of promotional categories. The point is that we want to provide an offer when a ...
-1
votes
0answers
17 views

Emailing a targeted DE attached to an email ID in Automation Studio

Is there a way to send a SQL Query activity targeted data extension attached to a particular email id in automation studio? The query activity will run daily and the automation studio will send the ...
0
votes
2answers
46 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
50 views

SQL Query to pull only Hard Bounces?

I currently manually update a database by exporting bounced email addresses using the code below: SELECT DISTINCT UPPER(EmailAddress) as EmailAddress, SubscriberKey FROM _Subscribers WHERE Status = '...
1
vote
1answer
31 views

Boolean always showing True for Marketing Cloud SQL Query

I have a SQL query that is always returning true for my boolean field even though the data in SF is showing false. Incomplete_Runtime_Data__c is the boolean field, all other fields return correctly. ...
0
votes
1answer
24 views

How to select last order on SF Marketing Cloud?

I have a database consisting of over 1 million orders, I'm looking to select the last order (indicated by OrderDate) for a customer (indicated as CustomerID). Here's what I've tried so far: SELECT ...
0
votes
0answers
16 views

Getting failed to release the resource in tSalesforceOutput_1 :com.sforce.ws.ConnectionException: Failed to send request to https://************

I am getting the error failed to release the resource in tSalesforceOutput_1 :com.sforce.ws.ConnectionException: Failed to send request to https://********--developer.cs107.my.salesforce.com/...
0
votes
0answers
29 views

Converting Lists into Data Extensions

Currently, all of our subscribers are in Marketing Cloud lists and not data extensions, and our team doesn't have the bandwidth to convert all of our web forms to have the data go into data extensions....
0
votes
1answer
25 views

How to update a field in target Data Extension in marketing cloud

I wrote a query to update a field inside a target Data Extension. The query will set the field to True when a subscriber has received an email within three days. The field has a default value of False....
0
votes
1answer
32 views

Creating master data view with multiple data views

I am trying to create one master data extension for multiple different event types like clicks, bounces, opens, and etc. Is there any documentation that leads toward this? Or if I have to join all ...
0
votes
1answer
23 views

Is there any developer console sort of feature available to check & preview salesforce marketing cloud sql queries?

In marketing cloud while running a sql query, to check results I have to create a data extension and store results into it. I want to know if there is any alternative available through which we can ...
0
votes
2answers
32 views

Week Number, where week starts on Monday (not Sunday)

I'm trying to calculate the number of new subscribers, unsubscribes, held, etc, by week where the week starts on Monday and ends on Sunday. I can get the query to work fine when the week starts on ...
0
votes
1answer
28 views

Populating Data Extension Column from Existing Columns [closed]

I would like to populate a Data Extension Column from existing columns Say I have a Data Extension Like this FN | LN | FN LN Jack|Johns| Null Jane|Jones| Null Luke|Smith| Null FN and LN are ...
0
votes
2answers
32 views

Personalisation strings exact target

We are new to MC and would like to know if there is a way to query all the personlisation strings that exists in all our contents. We are still using classic content but looking 5o move to content ...
0
votes
1answer
19 views

Why is my code querying All Subscribers producing results that don't exist in All Subscribers?

In Marketing Cloud - I'm trying to write an SQL that will pull everyone who has opened an email in the last 90 days. I'm getting results from the below SQL, but when I spot-check the data, I'm finding ...
0
votes
1answer
46 views

Select status from multiple lists with SQL

I have a group of email addresses I'd like to see which lists they're currently subscribed to, they're in "DE Name". I've tried the following SQL but the result is several duplicate rows and all ...
0
votes
1answer
24 views

Query to populate a field with different name in data extension

I am newbie in SQL, but i have to populate one data extension (name = TEST A) from another data extension (name = TEST B). The fields in 'TEST B' has name like: 1. Name 2. State 3. Email Address 4. ...
0
votes
1answer
98 views

SFMC - Querying All Contacts with SQL Query Activity (System Data Views)

I am trying to create a data extension that contains all contacts from my enterprise account. I need a complete list of contacts as we are billed by SFMC per contact. Querying the data view ...
0
votes
2answers
41 views

How to correctly handle timezone difference in data views from Marketing Cloud

I store the last open times for my emails in a separate Data Extension queried from the _opens data view. The problem I have is that I need to be able to see who's received an email today via a ...
0
votes
1answer
35 views

Inverting SQL query. What to use instead of EXCEPT in SFMC?

I have a pretty simple issue but my several attempts don't return desirable results. The following query updates the field of DE (noImax) with "true" for those who meet specified criteria. What I need ...
0
votes
2answers
59 views

Can't get my advanced SQL query to work

So the what I'm trying to achieve: At the moment a contact which is assigned to an order creates or change his address, a task will go out to owner of that order. The problem is that i refer to the ...
1
vote
1answer
62 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....
1
vote
2answers
44 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
24 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
51 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
37 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., $...
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
37 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
18 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
30 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
23 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
38 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
35 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
55 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 ...
2
votes
0answers
47 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
36 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
70 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
39 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
39 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
34 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
79 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
21 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
38 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
88 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 ...