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
1answer
39 views

SQL Query for getting distinct domain and count

I have a Data Extension which has email address. I want to store all distinct domains and counts of these emails in another DE For eg: I have someth@som.com, test@test.com, 123@test.com. So my o/p ...
0
votes
1answer
30 views

Is there a way to use SQL to identify all records who are not part of an active Journey in Marketing Cloud?

I am a novice when it comes to SQL. I'm trying to identify all records in Marketing Cloud that are not part of an active journey (ie. they have never been a member of one or have exited one in the ...
0
votes
1answer
32 views

Tips for using SQL skills efficiently in Automation Studio [closed]

As a Marketing Cloud marketeer I really see queries as very powerful in creating strong segments. So, I've dived into SQL and query writing using codeacademy and w3schools, but even though I find ...
0
votes
0answers
21 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 ...
0
votes
1answer
34 views

Query: Deduplicate results (from _Click data view query)

I'm trying to add click on a certain alias to a data extension for segmentation purposes, but I find my query erroring with the 'Violation of PRIMARY KEY constraint. Cannot insert duplicate key.' ...
0
votes
1answer
45 views

SQL PIVOT replacement in Marketing Cloud?

I have two tables: STUDENTS APPLICATION A student can apply to 1-3 programs. Student_ID is a column on Application. Table structure APPLICATION ID | Student_ID | Program__c 1 | 65432 ...
0
votes
1answer
32 views

Marketing Cloud SQL - cannot insert a NULL value into a non-nullable column

I'm using Marketing Cloud Connect and have synced across the Salesforce Contact object, which becomes a data extension. I have another data extension that I send related information to and I want to ...
0
votes
1answer
23 views

Adding contacts and leads to a DE and receive a Violation of PRIMARY KEY constraint error message when running query

I'm attempting to add leads and contacts into a DE from a synced data extension that receives contacts and leads when a quiz is completed. The quiz can be taken multiple times which is why there is a ...
0
votes
1answer
25 views

Is it possible to create a parameterized SQL SFMC activity?

I find it hard to believe that based on this documentation, input parameters cannot be passed into a Salesforce MC activity. I can use a journey builder activity to perform a row update, however I ...
0
votes
2answers
38 views

Using SQL to split a data extension into 4 mutually exclusive groups

I have a data extension that I need to split into 4 mutually exclusive data extensions. I would like to avoid using the Random data extension feature in Salesforce since I would like to refresh these ...
0
votes
1answer
35 views

Multi-part specification SQL error

I am trying to pull bounce information for subscribers of a particular data extension using the following SQL: Select o.JobID, o.SubscriberKey, o.EventDate, o.BounceCategory, o.SMTPCode, o....
0
votes
1answer
36 views

Query all contacts and get emails instead of only sub keys

I found this link Can I query to all contacts in contact builder and it was very helpful but it only returns the subscriber keys. I want to query emails for duplicates since contactkeys - there are ...
0
votes
1answer
36 views

Using SQL CASE statement to convert TEXT to BOOLEAN value

is it possible to change multiple field values when using CASE statement? I have a Text field with several values ( 5 differnt ones) and each of this value has its own Boolean field in tragt Data ...
0
votes
2answers
40 views

Query Studio Issue: Getting Email Addresses of All Auto Suppression List Subscribers

I am trying to get the email addresses of all subscribers of a specific Auto Suppression list. However, the issue is that the name of the column containing email addresses has a space i.e "Email ...
0
votes
1answer
18 views

SQL issue pulling all tracking stats based off the email name

I'm trying to pull together a DE that contains: total deployed, total opens, unique opens, total click, unique click, etc... There have been multiple deployments of the same email name and I'm trying ...
0
votes
0answers
29 views

transact-sql seems is outdated and not anymore being supported

As soon as I opened the official resource for Transact-SQL from the link provided on the official support page (bottom links), a notice from outdated appears at the top of the Microsoft page. This ...
0
votes
0answers
11 views

Append in query keeps re-adding same data

Below is my query, I have append selected in my automation and insert it into an FTP. I am querying data views. The problem is the same records keep being added every file creation (%%Year%%%%Month%%...
0
votes
0answers
29 views

Error while parsing query: SQL validates in another order

I keep coming across this error message in certain queries and have found that if I move certain elements of the query (without changing anything else), it validates fine. My latest example is below. ...
0
votes
1answer
29 views

link CustomerID with Order Date for every product SKU

I'm trying to link some tables to create an audience for a campaign but the way I link them doesn't give me the right audience. When I check from the last week, I get many more products over £1000 ...
0
votes
1answer
38 views

Record not in unsubscribe data view

When I query _Subscribers I can see a record with a status "unsubscribed" There is nothing related in the _Unsubscribe data view though (I've checked all Business units in the multi org structure) ...
0
votes
1answer
35 views

Create an automation to update Test accounts Status from “Unsubscribed” to “Active” [closed]

I am sure this is a thing, but we have a group of "test" email accounts that our QA group regularly tests. Of course the unsubscribe is one of those things they test, and often we have to reset the ...
0
votes
1answer
49 views

convert text field to number

I have a data extension with a text field that contains numbers like 25.00, 130.00. I'm trying to convert that to a number field so I can write a condition number greater then but because its a text ...
0
votes
1answer
31 views

If constructor in SFMC query

How to do if conditioning within SQL statements SELECT su.emailAddress, IF C.SubscriberKey IS alphanumeric THEN '' ELSE C.SubscriberKey AS SUBSCRIBERKEY FROM from [_CLICK] C join [_SUBSCRIBERS] su on ...
0
votes
1answer
19 views

Combine 2 Booking Tables [closed]

Simple question - customer has asked to combine 2 sets of booking data. The 2 sets are from different types of bookings: BookingA Table Fields: Booking_Arr_Date Booking_Date Booking_Status ...
0
votes
1answer
21 views

Case when in Query studio

I am trying to count customer and non-customer in my master-DE. Having a customerID means he is an customer. SELECT SUM(CASE WHEN a.customerID is not null THEN 1 ELSE 0 END) AS 'Customer', SUM(CASE ...
0
votes
1answer
43 views

Customer and Bookings DE - Save COUNT of Bookings in Customer DE?

I have 2 De: XYZ Customers XYZ Bookings Within both DE there is an email field with the below names: XYZ Customers email field is called 'EmailAddress' XYZ Bookings email field is called '...
0
votes
1answer
32 views

ORDER BY Event date when making an sql query inside MC

https://help.salesforce.com/articleView?id=mc_as_data_view_click.htm&type=0 Each view has some type of event-date creation. When creating my automation and appending the SQL query, I want use ...
0
votes
1answer
40 views

Date add unexpected results

This is my query: SELECT FirstName FROM Account_Salesforce WHERE (dateadd(year, (2020 - year(PersonBirthdate)), PersonBirthdate) >= dateadd(day, 7, GetDate()) AND dateadd(year, (2020 - year(...
3
votes
1answer
96 views

SQL Query timing out - Optimization Help?

I'm trying to run this as a query activity (writing to a data extension) but am finding that it's timing out. It's not all that 'heavy' of a query but it's not completing. I'm a database developer by ...
0
votes
1answer
66 views

Why is job's EmailName empty after being retrieved via SQL when it certainly has a value?

Below query is not capturing EmailName. Any idea whats wrong here? select c.AccountID, c.OYBAccountID, c.JobID, c.ListID, c.BatchID, c.SubscriberID, c.SubscriberKey, c.EventDate, c.Domain, c.URL, ...
0
votes
1answer
42 views

Query to find all the subscribers from a child business unit who joined during a specific time frame

I am trying to query all the subscriber from a child business unit who joined during a specific time frame. Here's my SQL below Select s.SubscriberKey, s.EmailAddress, s.DateJoined ...
0
votes
0answers
38 views

I would like to give value to a field with values ​from a field in another table

I have two data extension, in the first one I have an empty field called codigo, to which I would like to give values ​​that are stored in the second data extension in the IdCodigo field, with the ...
0
votes
2answers
47 views

Query to find date of subscriber's latest email

I'm trying to build a series of queries to keep a definitive list of all subscribers, along with the date of their latest email from us. I've almost got it working but not quite. The sequence is as ...
0
votes
1answer
33 views

Performing an Group By Function in SQL in Marketing Cloud Activity [closed]

I'm trying to perform a Group By function on my Click data view Data Extension, which was built from the _Click data view. (Not sure if it's necessary to have the extra layer.) I am looking to ...
0
votes
1answer
42 views

Updating a DE with no Primary Keys

This might be a stupid question but is there a way to update records (yes many records and duplicate ones) in a DE without a primary key? I am not the owner of the DE which is already structured for ...
2
votes
1answer
43 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
47 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
43 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) ...
0
votes
1answer
40 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
47 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
43 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
33 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
44 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
0answers
17 views

Repercussions of adding new field to journey entry data extension

I'd like to add a nullable field to a data extension that's used as an entry source for an active journey. I know this is possible, but what are the repercussions of adding the nullable field if the ...
0
votes
2answers
68 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
51 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
88 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
45 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
25 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
38 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
2 3 4 5
11