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
0
votes
0answers
11 views

IF NULL SQL returning error

I want a SQL query to return "VIDE" when the field is empty in Sales Cloud but I have an error message saying "Incorrect syntax near the keyword 'IF'. Here is my SQL part : IF NULL (...
0
votes
0answers
14 views

Composite Key aggregate function group by clause

I'm trying to find out how many emails have been sent to a customer in the last 10 days. I have a Data Extension with a composite Key, the number of emails recieved(CustomerID) and the number of email ...
0
votes
2answers
32 views

Unique Openers By Each Month

I have a query which pulls information about unique openers in the last 30 days, but I would like to find this info for each month from the last 6 months. Here's the query I wrote Select o....
1
vote
1answer
38 views

Getting duplicates from a data extension

We would need to extract duplicate values from our Marketing Cloud data extension and deliver them to our data team for review. The DE has about 34k contacts and only 3 fields: Account_ID, ...
2
votes
1answer
72 views

Using SQL query in Marketing Cloud with a hidden field in Sales Cloud

Is it possible to run a SQL query that uses a hidden field in Sales Cloud?
3
votes
1answer
136 views

An error occurred while checking the query syntax. Errors: Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax

I've had a few different kinds of query return the following error message, quite often when I'm using 'case when': "An error occurred while checking the query syntax. Errors: Old style JOIN (...
0
votes
1answer
56 views

How to select only the characters appearing before a specific symbol in a SQL Select statement

I have strings in a database like this: firstname.lastname@domain.com And I only need the characters that appear before the @ symbol. I am trying to find a simple way to do this in SQL.
0
votes
1answer
30 views

Getting all sends, and (unique) opens and clicks into one Data Extension

We would like to get the sends, (unique) opens and (unique) clicks from last 24 hours in the same data extension. I started with 2 LEFT JOINS, with the goal of getting all sends, and opens and clicks ...
0
votes
1answer
44 views

Marketing Cloud SQL - Invalid Column Name

After I added the Group by statement I got an error that now is saying the column name is invalid, but it does match with the table. Any other ideas of what might be causing the error? ERROR: An ...
0
votes
0answers
21 views

Dataclip Error ERROR: cannot execute CREATE TABLE in a read-only transaction

I just created a Heroku database but when I try and run a simple create table command in the I get the data clips it says: Dataclip Error ERROR: cannot execute CREATE TABLE in a read-only ...
0
votes
1answer
44 views

Identify records in data extension from CSV file [closed]

I am trying to identify a series of records in a data extension. I have a CSV file containing all the records that I want to identify in ly data extension but I don't know to proceed to clearly ...
1
vote
0answers
12 views

One automation with 2 SQLs triggering 2 separate journeys [closed]

We want to design an automation in Automation Studio that would comprise 2 SQLs. Both queries will populate data into separate data extensions, which we would like to use in 2 separate journeys as ...
0
votes
1answer
32 views

Deduplication based on non primary key field

I have a data extension that has duplicate email address and it has a primary key field. I need to deduplicate the data extension based on the email address. The rows that are duplicate have same ...
0
votes
3answers
72 views

Marketing cloud SQL Query Deduplication

I am trying to deduplicate my entry data extension based on the completeness of field values. For Instance, i have the a data extension with same email address, the record that has maximum field ...
0
votes
0answers
34 views

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

What I have: A source data extension with slightly over 14000 records Two attributes in the DE: GlobalID (Text 10) BirthDay (Text 8, YEARMONTHDAY, no dashes) The target data extension has ...
0
votes
1answer
57 views

Find active subscribers for each month for last 6 months

I am trying to find out the number of active subscribers who opened at least one email in the last 30 days, I would like to find this info for last 6 or 12 months, to monitor the growth of active ...
0
votes
3answers
69 views

SQL query: not working properly for blank values

I have an issue, where SQL queries do not work correctly for blank values. A very brief summary: I have TABLE A with 200k rows (no duplicates). In this table there is field called MailingCountry. ...
0
votes
1answer
21 views

Marketing Cloud- Extract click activity from a Cloud Page

We created a custom landing page with a custom form, which we used to collect customer orders stored in a data extension. The possible choices (type: radio button) were 30, 60 or 90. The subscribers ...
0
votes
2answers
48 views

DateDiff not working

I am using the datediff function and it doesn't seem to be working. What I am currently using DATEDIFF(year, creation_date__c, SYSDATETIMEOFFSET()) AS AnniversaryYears It just gives an empty ...
0
votes
2answers
45 views

SQLQuery for non Opens and Clicks for specific emails

I'm trying to build a DE of non opens and non clicks for two separate emails: EmailABC and EmailXYZ.. I;m getting errors when using this query.. Can you help.. Select distinct s.SubscriberKey, s....
0
votes
1answer
27 views

During overwrite, Marketing Cloud picks first PK record instead of prompting PrimaryKey violation

We have a peculiar case where we are joining 3 different data extensions (i.e. Consent_DE, Address DE and Channels DE) via SQL in Automation Studio. We have Global_ID as unique customer identifier ...
0
votes
1answer
61 views

Convert Birthdate to Getdate

I am trying to send a message to everyone whose birthdate is in two weeks but the date is of a different format and the query thus returns 0 rows. The query I am currently using is the following ...
0
votes
1answer
31 views

How do define what data updates in a data extension when using “Update” query in Marketing Cloud

I have a data extension that is built by a SQL query. The automation runs daily to add/update data. Its appointment data so what im trying to understand is what defines if a field is updated or not? ...
1
vote
1answer
93 views

SFMC Query Timing Out

I have a query that's assigns engagement definition to subscribers based on their interaction with the email. The dataViewSubscribers DE contains 16million users. However, the query times out now ...
1
vote
1answer
40 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 ...
0
votes
1answer
39 views

Union Opens, Deliveries, Bounces and Sents from pastmonth

I have one monthly automation by report that the report goes to ftp via CSV and after run an Import File that populates a Data Extension with the totally numbers of sents, opens, deliveries and others ...
1
vote
1answer
57 views

query Open or Click

I'm trying to get the customers who open or click an email and I'm not sure what I'm doing wrong. If I only join the Click or Open, it works but when I do them together, it is timing out which means I'...
0
votes
1answer
59 views

Birthday Journey with Sql query in Automation

I have created a Journey with Automation for birthday. I have a data extension with birthday field, using which I have created an automation to include only the customer's having birthday's on the ...
0
votes
1answer
37 views

Updating records from the base DE in Marketing Cloud

We are doing a big SQL that combines data from 3 synced data extensions (objects from CRM) and 3 local data extensions in Marketing Cloud. The data lands in the target DE, that's OK. However, we need ...
1
vote
1answer
36 views

Update data extension filtering on WHERE function

If we're building a data extension through a SQL query and it is set to Add and Update and we also use a where function like the one below: WHERE c.Optin__c = 'TRUE' If the Optin__c field is changed ...
2
votes
1answer
34 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
47 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
49 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 ...
2
votes
1answer
33 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
45 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
45 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 ...
0
votes
1answer
54 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
38 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), ...
5
votes
1answer
56 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
58 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
47 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 ...
2
votes
1answer
62 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
81 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
25 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
68 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
32 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
77 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
42 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
55 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 ...