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
26 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
0answers
33 views

SQL Query regaring duplicate values from one row into one single unique row [closed]

I want want to write query in marketing cloud which will populate single row from duplicate rows. Like : Emailaddress Interests games abc@gmail.com yes no abc@gmail.com no yes ...
0
votes
1answer
40 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
40 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
41 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
2answers
600 views

How to correctly handle timezone difference in data views from Marketing Cloud

I store the last open times for my emails in a separate Data Extension queried from the _opens data view. The problem I have is that I need to be able to see who's received an email today via a ...
0
votes
0answers
26 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
36 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
39 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
26 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
36 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
30 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
31 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
29 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
39 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
30 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
45 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
22 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. ...
2
votes
2answers
430 views

Journey Builder - Decision Split - Attribute Comparison delivers weird outcomes

I recently discovered a very odd mechanic inside the journey builder when it comes to decision splits and i am uncertain how to solve this issue. The example journey: What i want to do is the ...
0
votes
1answer
22 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
27 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
33 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
460 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
19 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
1k views

Delete Query on Data Extension

Can I run DELETE query under Activity > Sql Query. I need to delete few records from data extension based on field values.
0
votes
1answer
29 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
47 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 ...
1
vote
1answer
737 views

Query Journey Builder Sends

I want to run a query to pick up all subscribers who received a JB email in the last 7 days from a specficic Journey. I found this article but it is not filling the data extension! https://help....
0
votes
3answers
28 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
26 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
25 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
54 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
36 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
67 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
57 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
30 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
26 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
20 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
23 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
53 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
41 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
744 views

Query Journey Goals and Exits

There appears to be little documentation around these functions in Journey Builder, so hoping someone may have some insight. Can this data be accessed through the Journey Data Views or any other ...
0
votes
2answers
442 views

Marketing Cloud SQL Query to determine no of null columns

I am trying to execute the below query in a query activity. The intent is to find how many emails a customer record have. The query is executing without errors but the No_Of_Email column is not ...
0
votes
1answer
27 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
38 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
47 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
41 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.' ...

1
2 3 4 5
12