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
63 views

SQL Query to get CustomerID from two DE

I'm looking to write a SQL Query to get the CustomerID from Pot A and Pot B with a distinguish label so I can see that CustomerID 123 comes from A and CustomerID 234 comes from B. I have the ...
0
votes
3answers
316 views

How to write SQL query to pull only not matching Email IDs

I need to write a query to pull records which Email IDs are not matching in another table. For example, I have a two tables whose structures looks something like this: Table1 ID | Name | ...
0
votes
0answers
138 views

SQL Query activity Order by function [duplicate]

I want to order the result of my query result and put it via Query activity into a data extension (DE has no primary key). The order by MAWE is pretty important for me. select count (SubscriberKey) ...
1
vote
1answer
685 views

DISTINCT is not working as expected in Marketing Cloud

I'm experiencing problems with adding distinct in SQL automation. With SQL shown below I still receive duplicate clientids. Primary key would not work for me since during the next run of this ...
0
votes
2answers
55 views

Get subscribers with a changed status during last X days

We would like to send a list of subscribers' status changes both from "Active" to "Unsubscribed" and vice versa to SFTP. We would like to run this activity two or three times an hour to imitate real-...
4
votes
2answers
175 views

SQL Query on reverting data from DE based on a filter condition and modify it before i put it into another DE

I am trying to retrieve multiple Columns from a DE and split a column into multiple columns and insert into another DE along with the original column. Ex : I have a DE-1 with Full name, Email and ...
0
votes
1answer
59 views

Contacts and Orders Join - Primary Key Violation

I'm trying to pull in a list of contacts who've ordered within the last 2 days into a separate DE with the contact ID as the Primary Key. However, I've tried several SQL Join queries in Marketing ...
1
vote
1answer
75 views

Data View queries optimization

Some simple Queries joining together some Email Data Views are timing out and I'm having a bit of a hard time understanding why. They're simple and concrete enough.. Any ideas to tackle this would be ...
0
votes
2answers
114 views

Date 14 days before current date

I have a subscription program and I want to create a journey and send an email 14 days before the subscription program ends. In order to do that I need to run a query to get only the customers whose ...
0
votes
1answer
135 views

Unique opens and clicks from Child BU for child and parent account for past 1 year not working

I have tried basic SQL which is on help document to get last 30 days opens is not working. here is what i have Select j.JobID, j.EmailName, j.DeliveredTime as SendTime, o.EventDate as OpenTime, s....
1
vote
1answer
414 views

Marketing Cloud Query - Failure: Cannot Insert a NULL value into a non-nullable column

I've got a target DE with the following data privileges: ContactId = PRIMARY KEY Email = Nullable FirstName = Nullable HomeStore = Nullable State = Nullable I am currently running this query and am ...
-1
votes
1answer
76 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
2answers
59 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
968 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
224 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
235 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
508 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
151 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
955 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
1answer
78 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
15 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
102 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
400 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
788 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
619 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
2k 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
79 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
1k 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
118 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
127 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
93 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
77 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? ...
2
votes
1answer
180 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
274 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
61 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 ...
2
votes
1answer
121 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
453 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
58 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
49 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
430 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
1answer
605 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
751 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
634 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
100 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
148 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
892 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
437 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 ...
4
votes
1answer
793 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
497 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
356 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 | ...

1
4 5
6
7 8
15