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)

156 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
1answer
138 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
89 views

How to identify a SMS subscriber in mobile connect by a record in Sales Cloud?

As I understood, a subscriber will be added to mobile connect once the SMS was sent to the mobile number of that SFDC record. I tried to query the data to have a better understanding SELECT ...
2
votes
1answer
44 views

SQL: How to get the right SUM of two different columns from two different data extensions into one new DE

I have the following tables: In the third table I want to combine these data with the SUM option in SQL so the total of clicks and Sends are in one field. Like this: I created the following query: ...
2
votes
0answers
303 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
0answers
886 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
234 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
496 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
1k 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
87 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
2answers
1k 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
1answer
31 views

SFMC Dynamic Timezone Sends

Timezones are a concept I have a hard time wrapping my head around. I'm trying to build a dynamic timezone program that runs an adjusted sendtime on a daily basis. Everyone should be receiving an ...
1
vote
1answer
74 views

Query Studio - Could not convert date and/or time from string data type

On one data extension, I have a DateIn field modeled as text. The value can be in one of the two formats like: 2015-06-23 16:12:04.703 8/6/2018 11:46:59 AM When I cast the field to date and use it ...
1
vote
1answer
151 views

Journey Handling: Deleted or non existent subscribers

Scope: Journey Builder Main Problem: Dealing with deleted / unsubscribed and never existent subscribers inside a journey. These kind of subscribers shall not receive emails from E-Mail Activities ...
1
vote
0answers
40 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
389 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 ...
1
vote
1answer
328 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 ...
1
vote
1answer
278 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
319 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
32 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
187 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
598 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
0answers
59 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
347 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
110 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
152 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
171 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
1k 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
131 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
96 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
328 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
450 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
142 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
70 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
149 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
256 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
172 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
574 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
185 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
56 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
23 views

Marketing Cloud replace function in SQL

In my scenario I have to replace the new line character in a record for a particular column to some other character. I have tried below query but got the error. select CAST(REPLACE(REPLACE(CAST(a.[...
0
votes
0answers
53 views

SQL Query : Using JOINS with CASE Statement

I've 3 DE MASTER DE with fields SubscriberKey(PK), donottrack, Createddate, Modifieddate Staging DE with field CustomerID(PK), Createddate Reference DE with field donottrack default value false. ...
0
votes
1answer
42 views

How to get external key (customerkey) from an email trigger?

I want to get all external keys from email triggers in a journey for stopping/publishing/restarting. So far, I can get their JourneyActivityObjectID (TriggeredSendDefinitionObjectID) through the SQL ...
0
votes
1answer
13 views

Possible Bug - Business Unit Subscriber Status Data View has no records?

The BusinessUnitUnsubscribes Data View which is meant to contain the unsubscribe reason has no records. SELECT COUNT(*) AS count FROM [_BusinessUnitUnsubscribes] AS bu Returns 0. Is this data ...
0
votes
0answers
11 views

all subscribers per business unit - SQL code data views

I'm trying to get the number of subscribers per business unit using this code so I was wondering is this the right way to do it (using data views in Query studio): SELECT count(a.SubscriberKey) as ...
0
votes
1answer
35 views

I cannot "add" additional column to the OPENED Email Query

I'm trying to pull contacts who opened specific email. I'm beginner so I'm using the query i found in documentation that works but on top of that query I would like to add additional column. Here's ...
0
votes
0answers
45 views

Marketing Cloud Reports on Campaign based on the bounce rate

I'm looking for different possibilities of creating report in MC for a campaign based on the Bounce rate%. ex : I want to setup a report for xyz campaign where bounce rate is more than 5%. I know it'...
0
votes
0answers
39 views

Automation Studio - Violation of primary key constraint, Cannot insert duplicate key

I have an SQL query running in automation studio on a daily basis. It takes data from data extension A and puts it in data extension B. It functioned without any errors for 4 months, but it started to ...
0
votes
0answers
49 views

Query for all subscribers AddMethod, DateModified, DateCreated for mismatched emails with subscriber key in SFMC?

I'm going to start by saying this forum has been nothing but helpful and appreciate everyone taking the time to respond to others. This is my first post so I apologize if this is not following the ...