We’re rewarding the question askers & reputations are being recalculated! Read more.

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
2
votes
1answer
31 views

Can SQL select values in one table to match a field in another table with wildcards? (Value like %value%)

We have two tables - one is the _click table and the other is a catalog of ProductIDs and we'd like to find all the clicks from the previous day where the ProductID is in the LinkContent field. The ...
0
votes
0answers
40 views

GROUP BY in Marketing Cloud to get aggregate opens

We have queried _Sent and _Open data views to get the openers / non-openers for the last 30 days. In the second step, we would like to inner join it with _Job data view to get emailname and emailid. ...
0
votes
1answer
48 views

SQL Syntax for between dates

I have an orderDate record and I'm trying to get the records from between two dates at a specific time in Query Activity within SFMC: Start Date: Friday 6th October 07:00am Finish Date: Sunday 22nd ...
1
vote
1answer
24 views

How does Select Distinct work on a Primary Key in Marketing cloud's SQL?

How does Marketing Cloud's SQL in Automation Studio works when having a DISTINCT operator on a data extensions that has a Primary Key (in my case "Customer_Key")? Specifically, does it look for unique ...
0
votes
2answers
30 views

How to prevent a subscriber from entering multiple journeys at the same time in Journey Builder?

We have multiple journeys built using journey builder in our SFMC instance. We are trying to enforce a new business rule which should prevent the same subscriber from entering multiple journeys at the ...
0
votes
1answer
33 views

Filtering Opens and non-Opens in Data View SQL

We would like to get all opens data together with email sent/delivered data for the last 6 months. The goal is to separate openers from non-openers by filtering the resulting data extension. We are ...
-1
votes
1answer
44 views

Query to fill the empty column with the next available value from the Data Extension [duplicate]

I have a data extension with a customerID and 15 products assigned to them(screenshot attached) Some of the 15 products are missing the value and I would like to write a query so if Product1 is empty ...
1
vote
1answer
32 views

Marketing Coud: Always use CAST and CAST with date fields from Data Views?

When querying SF Marketing Cloud data views, specifically its date-type fields (e.g. EventDate), Salesforce support suggested we should always use SQL functions : CONVERT (e.g. varchar,EventDate, 20),...
4
votes
1answer
45 views

Error appending a Data Extension referenced in query

I have a query in a child business unit with an action type of 'Append.' This query contains a join, and it's targeting one of the Data Extensions referenced in the join. The query was originally ...
3
votes
1answer
44 views

Marketing Cloud Use SQL Activity to Join Multiple Data Extensions

I've recently completed an IP warming process. Contacts were being added to a new Data Extension before each send to keep track of the the separate imports throughout the migration. Now I would like ...
0
votes
1answer
41 views

Delete records from a Data Extension created by Journey on the basis of date

I have a journey in which entry criteria is Salesforce Data. Whenever a contact enters into a journey, the journey puts its details into a DE(created by the journey itself). I want to write automation ...
1
vote
1answer
37 views

SQL - Transpose/Pivot Rows to Column

I have a data extension with values like: Email_Address | Job_ID | Date | Etc wmcm@me.com | 1234 | 1/11/19 | etc wmcm@me.com | 5678 | 1/11/19 | etc wmcm@me.com | 9432 | 1/11/19 | ...
0
votes
1answer
75 views

Is there a BASE64 Encoding Function using SQL in Automation Studio? [closed]

We'd like to BASE64 Encode our Email Addresses so we can send them to an external third-party (it's the third-party's requirement). We know how to do it using AMPscript, but not in SQL. Ultimately, ...
0
votes
0answers
51 views

SQL Query WHERE Clause in automation not working

I have an automation that fetches a file from the FTP and populates a Data Extension. The query is working fine if I just do select from in the automation. One of the columns in the Data Extension is ...
1
vote
1answer
21 views

Additional Fields Added to _Open Query

I am trying to get tracking information on people who did not open a specific email campaign. I was able to get it to work using the below query: Select distinct s.SubscriberKey, s.JobID, s.BatchID, ...
0
votes
1answer
62 views

Marketing Cloud: Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object

Everyday we have an automation running to import new magento customers from an FTP file to our contact DataBase. Today, for the first time we had this error message. Query failed during execution. ...
0
votes
1answer
29 views

Querying all contacts from DE field in SFMC

Is there a way to query all contacts based on a data extension field? IE query everyone with the last name "Johnson" and the DE field is Last Name. Thanks in advance.
0
votes
1answer
67 views

SQL Query: looking for a particular email name and user it was sent to

i'm a newbie with SQL, and i wanted to pull out all subscribers that were sent by a particular email with open date send date and click date. @Bread2827 help me out with this query below but i can't ...
1
vote
1answer
33 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 ...
0
votes
2answers
45 views

SQL query to find subscribers who were sent with the same Email Name

I've been using marketing cloud for almost a year now but not really exploring the SQL queries since i'm not really familiar with this. I'm also aware that my friend set up my account with lots of ...
1
vote
0answers
40 views

Marketing Cloud SQL: boolean != 'True' doesn't work as expected

In Marketing Cloud, I have 2 data extensions. One holds subscriber keys and basic profile information that gets overwritten, one holds the subscriber keys and extended profile information that cannot ...
0
votes
0answers
33 views

Marketing Cloud: Convert STRING to INTEGER(NUMBER) using SQL

We would like to do a SQL query on a master data extension from which there is a data column which was set as "Text" instead of "Number". We would like to make a conversion into the target DE from ...
1
vote
0answers
22 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 ...
0
votes
1answer
73 views

Query DE with multiple Productid's to delete products that don't match another DE

I have this two Data Extensions and the ProductCode is the same as Product1, Product2, Product3, Product4...Product10 The problem is that there is some records of products in Product1, Product2......
0
votes
1answer
41 views

SQL query contacts don't exist in another data extension

I have: [Data extension A] with CustomerID(PK) [Data extension B] with SubscriberKey(Nullable) which has the same value as CustomerID Attempting to get all the customers from [Data extension B] ...
1
vote
1answer
48 views

Query to compare branches of a journey after a percentage split

I ran a journey where I used a decision split to separate the audience into 4 branches and then sent each branch the same series of emails with varying wait times in between sends. Now I want to ...
0
votes
1answer
36 views

Getting sent data for a specific time frame using Query

I am trying to pull send data from some scheduled sends. I am using below query: SELECT s.SubscriberKey as Email_Address, s.EventDate as SendTime, s.JobID as JobID, d.VER as VER, d.CellMarket as ...
0
votes
1answer
42 views

Multiple SELECT in subquery

In Marketing Cloud, we would like to check for the existence of records from one table in multiple other tables based on the unique identifier - Customer_ID. However, I'm not sure whether I can ...
0
votes
1answer
31 views

Automated way to set SEND VOLUME by day?

We have a list of 100K subscribers and we want to send a maximum number of emails per day - but the max volume changes by specific day. For example we want to send more emails Tuesday through ...
1
vote
2answers
64 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 ...
0
votes
2answers
45 views

How to know total Technical and Unknow Bounce from Journey Builder E-mail's

I noticed that the report from Analytics Builder does not have this field. Is there a way to know this with SQL? Only the report "Journey Builder Email Send Summary" shows me is the Soft, Hard and ...
1
vote
1answer
83 views

Query data extension with multiple columns to find the products that don't exist in another data extension

I have this two Data Extensions and the ProductCode is the same as Product1, Product2, Product3, Product4...Product10 The problem is that there is some Products in Product1, Product2...Product10 Data ...
0
votes
1answer
48 views

Marketing Cloud - SQL Group By returning all rows

I am trying to create a query to return only the last date of a set of records. The process is: A task is created in Salesforce and triggers a journey. In the task, agents can choose to send an SMS ...
0
votes
3answers
62 views

Query did not opens in the past 180 days

I have the following query to find all subscribers that have not opened in the past 180 days: Target Data extension has: SUBSCRIBER RELATIONSHIP ContactKey relates to Subscribers on Subscriber Key ...
1
vote
2answers
46 views

Marketing Cloud SQL Case Function

I have an already existing SQL function running in Marketing Cloud and have been attempting to add on a CASE function to determine a column of "State". I have tried a couple different versions of this ...
1
vote
0answers
36 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
1answer
118 views

Replace semi-colon not allowed in SQL - alternatives?

Looking for solutions to my Replace() issue outlined here, I thought an alternative might be a replace within SQL when moving data to the deployment data extension. However, I've discovered that we ...
0
votes
1answer
95 views

Converting date/time to string in Marketing Cloud's SQL - string to date conversion error

We are trying to convert a string field to a date field using the CAST function in Marketing Cloud. Our goal is to use the newly converted date records to segment our subscribers based on the date of ...
0
votes
1answer
53 views

Select Records from Different Sources

I am trying to grab email address from different sources for selected Account IDs. I have all email address in Contact table those are grabbed from Account and Opportunity table. So task is first I ...
0
votes
2answers
46 views

marketing cloud soql filter by row number

I've a DE which has 2M records and would like to split it to 1.1M and 0.9M using SQL query. A query like below throws error. Any thoughts? select A, B, C, ROW_NUMBER() OVER (ORDER BY ...
0
votes
1answer
39 views

Excluding parenthesis using where clause

How to exclude values, where values contains "(" paranthesis. For example I tried, Where Email not like '%(%' But I am keep getting an error. An error occurred while checking the query syntax. ...
3
votes
2answers
45 views

Sql Action “Run Once” fails to pull records

I'm trying to run a query that contains a few joins. It is populating a data extension. However, when I "Run Once", not only does it fill the extension with data, but there is no record in the ...
1
vote
1answer
36 views

Find Subscriberkey based on Subscriber ID

I have a daily file with only Subscriber IDs sent from Audience Studio into Salesforce Marketing Cloud. I have imported the file into a data extension called 'SubscriberID'. I will need to find the ...
0
votes
1answer
78 views

Get all records inside a given date

I need to get all the records that are inside a given date. What I have now is giving me only one of them randomly every time I run my query. Any suggestions on how to fix that? SELECT table1.Number ...
0
votes
0answers
28 views

SSJS to bulk update SQL Activities

I have many SQL Query activities each of which i need to update the query text to a) join a data extension b) add to existing where clauses to exclude the joined DE where the unique field IS NOT NULL. ...
2
votes
1answer
61 views

What exactly the “BatchID” is in tracking Data Views?

Lately, I have been trying to figure out the most generic query for getting tracking data into one table. As suggested in the SQL Reference, I have been using this join: ... FROM _Sent s LEFT JOIN ...
3
votes
1answer
58 views

Automation Studio Query not accepting parentheses in combined AND OR

We've run into a problem which prevents us from combining ANDs and ORs in the WHERE clause of a SQL statement. This is the syntax of our WHERE statement: SELECT s.subscriberKey FROM ...
0
votes
0answers
13 views

Excluding test sends from Email Open Data View query [duplicate]

we set up sth. like a custom lead scoring in Marketing Cloud by using web webtracking and data views. We are gathering everyone who e.g. opens/clicks an email, visits a specific page on the website ...
0
votes
1answer
65 views

Discrepancy in tracking data from data views

We are using the following query to get tracking data into a staging data extension. The data we are getting is looking weird - the open and click dates have a timestamp that is earlier than the ...
0
votes
0answers
30 views

Checking Filter Condition in Another Data Extension in Marketing Cloud

In the Marketing Cloud parent BU level, to which our external data comes in, we have 2 data extensions: one is "Customer_Data" and the other is "Master_Consent". Our goal is to "download" the entire ...