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

Case When Substring not applying logic correctly

I have a field in SFMC that is a concatenation of two julian dates and a character. I need to split out the dates and convert them to gregorian dates in two seperate fields. However, the julian data ...
0
votes
1answer
19 views

SQL Query returning null values for Boolean fields from Data Extension

I have 2 Standard Data Extensions and an automation written to get values from one DE to other. The issue I am facing is Boolean values from one DE is not copying exactly in the other DE. False values ...
0
votes
1answer
26 views

Convert Julian Date in a string to Gregorian Date format [closed]

I have a text field that contains a string of two julian dates and a hyphen. Example:20140-20160 Structure: YYDDD-YYDDD I need to be able to output the two julian dates into two date type fields. ...
0
votes
1answer
26 views

SQL query not updating the target data extension

I am trying to run a sql query in Automation studio MC for syncing my Sync DE( Contact_salesforce) to a local DE. I want to send email to tht=at local DE in the next automation. But I am facing issue ...
0
votes
1answer
21 views

Marketing Cloud Query Activity

I have tried to run the below query in marketing cloud and it has thrown below error. "error saving the query field.an expression of non-boolean type specified in a context where a condition is ...
0
votes
1answer
25 views

Is possible to increase timeout for automation studio?

I have several SQL activities that are facing timeouts and I would like to know if it is possible to increase the time to get timeouts and why? Thanks
0
votes
1answer
17 views

Uniqueness of TriggererSendDefinitionObjectID

I'm trying to create an automation that will report on the performance of certain metrics for emails that belong to a Journey. What I want to know is how unique is TriggererSendDefinitionObjectID when ...
0
votes
1answer
41 views

Compare current time (hours) with time range in Transaction SQL

I need to capture the time of a file drop in an automation in SFMC in hours. So, I think I will have to capture time stamp as now() in hours. Then I have to compare the current hour to a time range, e....
0
votes
0answers
12 views

Extracting and exporting (file transfer) SQL query from automation/data extension in 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
26 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
15 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
32 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
35 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
35 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
11 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
47 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
31 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
41 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
46 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
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
40 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
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
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
27 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
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
1answer
43 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 ...
1
vote
1answer
36 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
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 ...
0
votes
2answers
47 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
25 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
31 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
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
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
34 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 ...
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
31 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
51 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
1answer
41 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
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
2answers
60 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'...
1
vote
1answer
64 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
28 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
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
27 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 ...

1
2 3 4 5
12