What's the deal with Deno? We talk with a major contributor to find out. Listen now.

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
1
vote
0answers
779 views

SFMC: SQL Query on Dataview objects to calculate Journey's sends, opens, and clicks

I want to calculate the sends, opens, and clicks of all journeys. Expecting the records in Date Extension consists of records in the following sequence: JourneyActivityObjectID VersionID ActivityName ...
0
votes
1answer
45 views

Exporting a file from a data extention automation in salesforce marketing cloud

There is a database extension in salesforce marketing cloud that I need to export from the automation. I didn't create the DE and am unsure how to export it for use outside of salesforce marketing ...
0
votes
1answer
16 views

Why does my conditional average query return 0 in MC Query?

I have a table that I am trying to calculate average values based on conditional statements. I have used conditional statements before so I know the function is supported, but for this particular ...
0
votes
1answer
42 views

Multiple Random Selections Using Union- erroring

I'm trying to get to a random 31,500 from one segmentation group and another random 31,500 from another segmentation group (and so on) in SFMC automation studio using SQL. I know I could create ...
0
votes
1answer
39 views

SQL Case Statement

I'm trying to write a SQL Query for the below. I have list of email address in table. I need to pull Subscriber key associated to that email address from another table if that email address is not ...
0
votes
1answer
39 views

Automation to check if the email is opened or clicked

How do I check and assign 'Yes' value to the 'open' and 'clicked' row in my DE that I retrieved in _sent data view. This is my current code: select j.JourneyName, j.VersionNumber, ja....
0
votes
0answers
16 views

Top 15 most recent JOBIDs for a subscriberkey

I am trying to write a query that can help us identify out of the 15 most recent JOBIDs a subscriberkey received, how many were opened? My thinking is to break this up into a few queries: 1. A query ...
0
votes
2answers
60 views

How to trim a particular word from a string and store it? [closed]

I am storing the URL given below in a field in DE: https://www.tbd.com/tbd-bags/?utm_source=sfmc&utm_medium=email&utm_campaign=_4in1tbd_20170421&utm_term=nav4+bags&utm_id=9087&...
0
votes
2answers
33 views

Create an automation without a PK

I'm trying to create an automation with a file import to update a data extension. The problem is that my data extension doesn't have a PK and in order to create a query activity to populate the data ...
0
votes
1answer
49 views

How to create a SQL query using CloudPage input?

I have a simple CloudPage using the Smart capture form. The form collects a number and writes that number into a column called 'input_number' in a data extension. Is it possible to fetch the '...
1
vote
1answer
50 views

How to determine when someone has been removed from a campaign (REST API)

Similar to this question, I'd like to be able to fire off a query (from an external application) to determine when someone is removed from a Salesforce Campaign. The question I linked has a good ...
2
votes
1answer
90 views

How to parse json using SQL in Marketing Cloud

I'm looking to use SQL, in an Automation, to parse JSON data which is a JSON Array. I have 5M+ rows where I plan to parse the JSON data and write it to another DE where each id value is a separate row....
0
votes
0answers
27 views

Query with no error in synthax doens'tgive any result

I have written this query: SELECT Client_Id__c, Abandoned_Cart_Id__c, Status__c, Total_Amount__c FROM Abandoned_Cart__c_Salesforce with Overwrite option. and the synthax is correct. The issue is ...
0
votes
1answer
39 views

How to identify the % of time between two dates + how to find records between two dates using SQL [closed]

Relating to my question related to finding a date X% between two dates using SQL, I'm having a challenge resolving two other similar things: How to identify the % of time between two dates. Here's ...
0
votes
2answers
59 views

Inner Join with Data view and data extension in SFMC not working

I am trying to do a inner join with one data extension and the data view _sent, inner join on subscriber key from _sent and contactid from data extension. I want to fetch the max event date i.e the ...
0
votes
0answers
32 views

Error in query with Synch DE

I have synchronized two De from sales Cloud successfully and I'm now trying to run a query using them. I get an error message which tells they are unknown. I wonder which can be the error causing ...
0
votes
0answers
30 views

SQL for creating a sendable DE from three DE

I have to create a sendable data extension, which joins three DE: - Contacts DE - Abandoned cart DE - Cart Items DE The three DE are linked in an attribute group. I have written the query selecting ...
0
votes
1answer
51 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
1answer
36 views

Data View Counts not Matching FTP upload counts

So I am exporting data views using the automation studio for each of the data view objects (open, click etc.) to FTP then from there our backend guy takes these csv files and inserts them into another ...
0
votes
1answer
34 views

Ascending order date for each customer [duplicate]

I'm trying to see the order date in an ascending order for each customer and also how many order dates each customer has so I know how many times a customer has ordered. I've tried order by date but ...
1
vote
1answer
74 views

Joining _JourneyActivity and _Sent Data Views in Salesforce Marketing Cloud

Did anyone here manage to successfully join the data from _JourneyActivity and _Sent data views? The documentation says that they can be joined on _JourneyActivity.JourneyActivityObjectID = _Sent....
0
votes
1answer
44 views

Customers that only made one order

I'm looking to write a SQL Query to get the Customers that made only one order after the first of March. So if they ordered twice after the first of March I want to exclude them. I have the following ...
0
votes
1answer
31 views

How to find a date X% between two dates using SQL [closed]

I need to find what the date is X% between two date fields. The use case is I want to send an email to someone who is 70% of the way through their tenancy. For example, Move-in date = 01/01/20 Move-...
0
votes
2answers
70 views

Checking for a non ascii character in SQL Query activity

I have certain records in my DE, where the first name contains '�'. I wish to extract such records. Obviously a like wouldn't work. Is there a way to do this in SQL Query Activity.
0
votes
1answer
27 views

“Old style JOIN (ANSI JOINs) syntax” error in a simple query without subqueries and joins

Validating the following query: SELECT GetDate() as Valid_From, 'Lorem Ipsum' as Filler FROM SampleSourceDE shows this error: An error occurred while checking the query syntax. ...
0
votes
1answer
32 views

add hours to timestamp / sql activity

I want to add 7 hours to a timestamp value and write the value into another data extension. How can I add these hours? My poor first try: SELECT DATEADD(HOUR,+7,dateregister) AS ...
0
votes
0answers
29 views

Deleting test and dummy data from all subscribers

For some business reason(don't ask me why), our parent BU has few (near about 50k) test subscribers (who's subscriber key looks like @ourdomain.com) and some duplicate subscribers with _1, _2, _3, ... ...
1
vote
1answer
41 views

SQL Query Timeout Salesforce Marketing Cloud

I have a query running in Automation studio that times out. The dataViewSubscribers data extension contains 20 million users. Is there a way this can be optimised? SELECT ds.emailaddress , case ...
1
vote
3answers
529 views

ExactTarget - SQL Query that Pulls All Sent Emails with Open stats

I've been tinkering with this for some time and cannot get it to work. I have a data extension with the fields: [EmailName], [EmailAddress], [JobID], [SubscriberKey], [DeliveredTime], [OpenDate] I ...
0
votes
2answers
20 views

How to search a parameter through a different fields in a query string

String leadPhone = lead.phone; String leadEmail = lead.email; String qry = 'SELECT a.Id, a.Name,' +'a.Email__c,a.Birth_Date__c,a.Mobile_Phone_Number__c,' +'a....
0
votes
1answer
33 views

Retrieving data from SendLog and DataViews simultaneously

I am attempting to reliably retrieve data from both the Data Views and from the Sendlog. The reason is that I need to be able to add two attributes that were added to the SendLog to the DataView data. ...
0
votes
2answers
70 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
42 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
3answers
29 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
27 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
1answer
33 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
2answers
88 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
48 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'...
0
votes
1answer
82 views

SQL query from _subscribers is not retrieving any records

I have below SQL query in Automation: select top(10) emailaddress , dateunsubscribed , status , subscriberkey , subscriberid from _subscribers It doesn't populate any records in Data Extension ...
1
vote
1answer
91 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
33 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
29 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
17 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
21 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
25 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
139 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
50 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
29 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
43 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
102 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
2
3 4 5
13