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)

138 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
1answer
91 views

Print reason for SQL query failing in SSJS

I built a Microsite which staff can you use to run one-off queries without having to create an activity and then run it or place it in an automation. It works great but is very basic - when you click ...
2
votes
0answers
119 views

Reliable Salesforce JDBC to be used with DataGrip

Do you recommend any particular JDBC for Salesforce (can be paid) which is reliable enough to be used for commercial purposes along with DataGrip (or similar) software? I've been checking similar ...
2
votes
2answers
636 views

Journey Builder - Decision Split - Attribute Comparison delivers weird outcomes

I recently discovered a very odd mechanic inside the journey builder when it comes to decision splits and i am uncertain how to solve this issue. The example journey: What i want to do is the ...
2
votes
0answers
484 views

Marketing Cloud SQL ORDER BY not working

I am at the Parent Enterprise Level and trying to sort the SubscriberKey column from the All Subscribers List into a Target Data Extension: All_Subscribers_DE. The columns are populated but the ORDER ...
2
votes
0answers
158 views

Left Join query

I'm trying to left join a table in Marketing Cloud Automation Studio Query. I don't receive any errors but the expected data is not pulling into the data extension. The bolded statements are what are ...
2
votes
0answers
331 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 ...
2
votes
0answers
804 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 ...
2
votes
1answer
85 views

Two vastly different counts using queries found here… what's the difference?

As a SQL neophyte, I know enough to be dangerous. I have two queries, both found here... the first is the one I'm directed to to answer "who hasn't opened an email in xx days". SELECT s....
2
votes
0answers
612 views

How Do I Properly Offset GETDATE() in Marketing Cloud Query Activities for US Eastern Time Year-Round?

I want to use a Query Activity within Automation Studio to find all of our app's users who last logged in between 24 and 25 hours ago. When I run the below query into a data extension, I instead get ...
1
vote
0answers
31 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. ...
1
vote
1answer
48 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
116 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
113 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
296 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
346 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
0answers
57 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
203 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
72 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
115 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
114 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
817 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
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
81 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
65 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
201 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
949 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
230 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
115 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
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
338 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
88 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
vote
0answers
255 views

query counts of 5 different data extension fields

We are looking to query counts of subscribers that unsubscribed from 5 separate fields (or unsubscribe buckets) in our master data set within the past day. We need to ultimately see counts of how ...
1
vote
1answer
144 views

Can i target specific audience in a data extension for an email?

I have a data extension in which i have a list of email address of customers. In that data different types of customers. I know one option is to create filter if i want to send email to specific ...
1
vote
0answers
417 views

Total sent in last 7 days from data view

I am using this query to pull how many emails were sent in last 7 days. Select count(DISTINCT JobID) AS TotalUniqueSent FROM _Sent WITH (NOLOCK) WHERE DATEDIFF(day,EventDate,GETDATE()) <= 7 But ...
1
vote
1answer
109 views

Marketing Cloud - Replacing Multiple hardcoded values in JOINS with values from Parameter Table

Salesforce Marketing Cloud query activities do not allow variables or temporary tables according to the "SQL Support" section of this official documentation (http://help.marketingcloud.com/en/...
1
vote
0answers
20 views

Marketing Cloud User and Role data pull

Trying to pull user and role data to cover t eh following. Is there any Reports or SQL out there that can help with this? List of all users Business Unit and what roles are assigned to each user ...
1
vote
1answer
53 views

Pulling data from DE on multiple values of a variable

I have a requirement wherein I have to query a database for the following condition. Database [Report Pull] has let us say 2 columns CustomerID ReportDt I have have to find all the customers ...
0
votes
1answer
4 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
0answers
6 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
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 ...
0
votes
0answers
23 views

SQL activity fails in Automation

I have a DE with 5 Primary Key columns. We are trying to insert data into DE by the SQL activity in Automation Studio with UPDATE Data Action. First time the data was loaded correctly, second time ...
0
votes
0answers
36 views

Query a column in a data extension with multiple values

I need to join the ProdutID from this Data Extension with another one from a different DE. Is there a way to do that considering that I have multiple values/fields under one column?
0
votes
1answer
39 views

Update Data Extension with SQL Marketing Cloud

I have a data extension (Name: Factura_T_Alex) with a single primary key (Name: NrFactura) and an attribute (Name: DataEmitere) which is a DateTime type. I need only the date part to use in a script ...
0
votes
0answers
36 views

What is the SQL for linking the _DeviceId field in _PushAddress to the SubscriberKey in _Subscribers

We have configured our App to register for mobile push using the SubscriberKey value that exists in Marketing Cloud. When we view Contact in the Mobile Push channel of All Contacts I can see the ...
0
votes
0answers
34 views

Case When Substring not applying logic correctly

I have a field in SFMC that is a concatenation of two julian dates and a character. I need to split out the dates and convert them to gregorian dates in two seperate fields. However, the julian data ...
0
votes
1answer
50 views

Extracting and exporting (file transfer) SQL query from automation/data extension in Marketing Cloud

There is a database extension in salesforce marketing cloud that I need to export from the automation. I didn't create the DE and am unsure how to export it for use outside of salesforce marketing ...
0
votes
1answer
52 views

Exporting a file from a data extention automation in salesforce marketing cloud

There is a database extension in salesforce marketing cloud that I need to export from the automation. I didn't create the DE and am unsure how to export it for use outside of salesforce marketing ...
0
votes
1answer
16 views

Why does my conditional average query return 0 in MC Query?

I have a table that I am trying to calculate average values based on conditional statements. I have used conditional statements before so I know the function is supported, but for this particular ...
0
votes
1answer
45 views

Multiple Random Selections Using Union- erroring

I'm trying to get to a random 31,500 from one segmentation group and another random 31,500 from another segmentation group (and so on) in SFMC automation studio using SQL. I know I could create ...