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
2answers
318 views

Skip records with duplicate Primary Key while querying data using SQL activity in SFMC

I am trying to query some specific email activities using SQL Activity and Data Views in SFMC. I am trying to fetch data from Subscriber, Sent and Job data view. I need EmailName, EmailAddress, ...
0
votes
2answers
64 views

calculate day difference between orders [closed]

I need to calculate the day difference between orders to be able to calculate the next prediction date of this order. I'm now focusing to find the day difference between orders. My assumption was to ...
0
votes
1answer
139 views

Marketing Cloud ContactID, where do I find this?

how do i access the "contactID" in SQL in the Marketing Cloud ? what data view is it in? when looking at contact builder I see data like this. Contact Contact ID: 1030837 Contact Key: ...
0
votes
3answers
39 views

Does Salesforce indexes work in the same way as a SQL indexes?

I mean SQL indexes generally speed up retrieving and slow down inserting. Does Salesforce indexes work in the same way as a SQL indexes?
0
votes
1answer
37 views

Query All Subscribers for SubKey based on email address in DE

I have email addresses in a Data Extension and am trying to query All Subscribers to pull in their Subscriber Key. I wrote my Query, it validates, ran the SQL and it returned no records. Not sure ...
0
votes
2answers
351 views

Identifying records in MC to delete

I'm trying to delete a large number of records from SFMC to avoid being charged extra. I've run several different SQLs to help identify the records I don't need, but when I run the Contact Count ...
0
votes
1answer
346 views

Query to find unique openers in last 30 days

I am trying to find out users who opened an email in the last 30 days from users who received one. Can someone help me verify if this is the right way to do it? I found few query examples and few I'...
1
vote
1answer
230 views

SQL Query Max Order Date

I'm trying to find customers whos last order date was exactly 3 months ago. The Data Extension I'm pulling from is called 'Purchase' and has the orderDate and email for every order a customer has made....
0
votes
1answer
85 views

How to identify all records in MC that are not in a Synchronized Data Extension

I recently synched over a large number of Salesforce Leads for an email campaign. I now want to remove those Leads from MC. My first step was to adjust the sync settings in Contact Builder, which is ...
0
votes
1answer
43 views

SQL NOT LIKE PARAMETER

I have the below queries in my automation. Query 1: SELECT s.SubscriberKey, FORMAT(DATEADD(hh,6,s.EventDate),'MM/dd/yyyy hh:mm:ss tt') AS EventDate, s.JobID, s.batchID AS BatchID, s....
0
votes
1answer
77 views

SFMC: How to add a prefix to values in a column

We are working on a use case where we needto add a prefix to our ID column. E.g. we have the real ID - e.g. 123xyz - and it shall have a prefix STAGE>> STAGE_123xyz is the final ID. Every record in ...
0
votes
1answer
20 views

Receive first stage but didn't receive the second stage

I have a campaign to email customers when the products are back in stock that is in 3 stages. First stage is "Confirmation" where you receive an email once you make a request for a product that is ...
0
votes
0answers
36 views

Increase number of records when I join

I'm trying to do a left join on data views _Sent, EventDate but after I run the query I get about 5 times more records and I don't understand why. In my data extension I have 400.000 records and after ...
0
votes
1answer
74 views

How to GROUP BY + Merge values of a column together within a SFMC data extention

here is the problem I am facing. I essentially need to group and then take the values of any unique user, and combine them and delimit them out by " | " into the result for each product. Any idea how ...
0
votes
1answer
630 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
92 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
90 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 ...
2
votes
0answers
294 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 ...
1
vote
1answer
84 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
115 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
2k 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
36 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
389 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
405 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
170 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
212 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
544 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
546 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
29 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
36 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
1answer
34 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
416 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
43 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
379 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
76 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
24 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
104 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
70 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
289 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
43 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(...
4
votes
1answer
129 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
92 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
500 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
296 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
312 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
235 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
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
305 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
129 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
3 4
5
6 7
15