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)
914
questions
-1
votes
1
answer
34
views
SQL filter on Date doesn't work
In my Data Extension I have a column named 'CreatedDate'. This is formatted like this: Wednesday, May 03, 2023 9:20 AM
In my automation I want to select only records since 01/01/2023. Time parameter ...
- 1
-1
votes
0
answers
11
views
SQL using ENT.Contact_Salesforce_1 - want to add Contact Created Date
I have a SFMC query that uses Salesforce contact data. ENT.Contact_Salesforce_1
I want to bring contact created date and created by. Is this possible?
0
votes
1
answer
27
views
Extending my use of SQL and UNION to add information about source
I'm doing segments based on SQL and UNIONs, so ie.
first I determine everyone having interest in product group X based on 'website interaction'
next I determine everyone having interest in product ...
- 531
-1
votes
0
answers
36
views
SQL to pull who unsubscribed a contactId a.k.a a subscriberkey from all subscribers
I'm looking to build a SQL to pull who unsubscribed a contactId a.k.a a subscriberkey from all subscribers list.
The challenge I'm facing is that I don't know who touched that contact key and ...
0
votes
1
answer
44
views
Marketing Cloud SQL - Can we do maths using field values
I have a scenario which I want to understand if it's possible
I need to pull all contacts which are in a 'waitlist' where a matched property has a status of Available (all good so far)
Where it gets ...
0
votes
1
answer
47
views
Fixing email address discrepancies between Salesforce and All Subscribers
I'm looking to do this automatically using SQL, data extract and import activities in an automation that runs hourly.
My query looks like this:
SELECT
C.Id as SubscriberKey,
C.Email as ...
- 571
0
votes
1
answer
46
views
Is it possible to run an automation only when a data extension is re-populated with data? Not knowing when data is coming
Background: Currently, we have an automation job where audience is pushed to FTP and automation trigger is based on file drop. When file is pushed with a set naming convention on FTP, automation ...
- 173
-2
votes
1
answer
35
views
How to add categories based on two different variables in SQL Query in SFMC
I'm trying to create a data extension that extracts subscriber key and clicks based on specific criteria. Below is the script:
select c.[JobID]
, c.[SubscriberKey]
, c.[URL]
, j.[...
0
votes
1
answer
50
views
I don't understand why my query result counts my email click wrong
Yes that looks better. There was still another issue. When clicking in an email it multiply the sends. This was because of the extra records in the clicks data view. I fixed it with the following ...
- 133
-1
votes
0
answers
28
views
TOP SQL Returns Incorrect Amount When Performing Multiple Joins in Marketing Cloud
I have a query that has multiple joins and uses the TOP clause to pull back 3k contacts but for some reason the number being returned is always less than 3k and, in most cases, not even close. Total ...
- 1
0
votes
1
answer
50
views
How to get records from sendable audience who did not make it to _SENT log without hardcoding the jobid
I have a DE extension where I have saved 1 record by querying:
DE NAME: DataView_Job_DE
Select top 1
JobID
,EmailName
,CreatedDate
,JobType
,JobStatus
,Row_Number() OVER (Partition by jobid ORDER BY ...
- 173
0
votes
1
answer
44
views
How to selecting value from another data extension in select statement in SFMC
I have a DE extension where I have saved 1 record by querying:
Select top 1
JobID,
EmailName,
CreatedDate,
JobType,
JobStatus
from _job
Output is saved in data extension: DataView_Job_DE
Now, I am ...
- 173
0
votes
1
answer
53
views
Marketing Cloud: ERROR: Violation of PRIMARY KEY constraint. Cannot insert duplicate key
I created this query and now that I put it in an automation, but I keep getting the error:
ERROR: Violation of PRIMARY KEY constraint. Cannot insert duplicate key.
MY SQL:
SELECT DISTINCT J....
0
votes
1
answer
82
views
I want to search the list of SQL Query Activities which uses my target data extension in Select statements without explicitly storing them in DEs
I'm trying to display the results in the form instead of storing the details in data extension which makes it accessible for everyone with the cloudpage URL.
Here the search is not happening as ...
- 3
0
votes
1
answer
45
views
Open rate for each link
I need to validate the open rate of each link that has a journey, I tried by sql query with this code.
select
c.subscriberKey as 'ContactID',
ja.ActivityName as 'EmailName',
c.EventDate as 'ClickDate',...
0
votes
1
answer
41
views
SQL Query Not Working
I'm trying to have this query pull all subscribers into a data extension who are not within another data extension. I'm not sure why it's not working.
Select Status, Name, FirstName, LastName, ...
- 3
0
votes
1
answer
40
views
How to extract List of all journey only last version with sql
My goal is to create a Data Extension with SQL that will contain a report of all Journeys existing in production BU (except for those deleted), but I need the latest version.
I managed to extract all ...
- 1
0
votes
1
answer
32
views
Get only the Clicks that has not been retrieved into the data extension
I have some emails that has been sent to the end customers and I want to track their interactions with the email which I have to send it back to the Sales cloud (CRM).
Following are the steps:
Step 1) ...
- 27
0
votes
1
answer
52
views
query received same email multiple times
I have a data issue where we have duplicate emails under different Subscriberkey.
I'm trying to build a query now to check how many of this duplicate emails have received the same email multiple times....
- 429
-1
votes
1
answer
64
views
Query for records added in the 30 day
I would like to check how many records added to all the BUs in the last 30 days.
SELECT SubscriberKey,DateJoined,EmailAddress FROM _subscribers
I just want the counts, with respect to BUs.
This will ...
0
votes
1
answer
45
views
RE: Query Help to pull from main DE
i created a query to select from the master audience DE that do not exist in the CRM file. However, when i go to check the DE only subscription status pulls in... do i have something incorrect?
Select ...
- 1
0
votes
1
answer
48
views
Specific Domain SQL Query result incorrect
I am trying to pull list of emails for a particular domain (which is aproblematic domain) and somehow my results does not make a sense--meaning that I'm getting way less than expected. To further ...
- 21
0
votes
1
answer
31
views
Why is my query counting an open for a send
I have the following query:
SELECT
sl.randomAB,
sl.JobID,
COUNT(DISTINCT sl.SubscriberKey) AS TotalSent,
COUNT(DISTINCT o.SubscriberKey) AS TotalOpened,
COUNT(DISTINCT c.SubscriberKey) AS ...
- 133
0
votes
1
answer
59
views
How to use domain exclusion DE via SQL
I have de created out of domain exclusion template and de contains only one field 'domain', and have inconsistent values like 'gmail.com' '@hotmail.com' '%yahoo%'.
I would like to use in SQL as ...
- 549
-1
votes
2
answers
48
views
Add a date field to SQL and convert the formatting to 'yyyy-MM-ddThh:mm:ss.fffZ'
I have an SQL running off a data view tabe and I need to add the ExtractDate to the target Data extensio.Date field doesn't exist in this Dataview tablem so apart from adding it, I need to convert the ...
- 9
0
votes
1
answer
58
views
Adding device OptIn field to SQL query in Mark. Cloud
I have SQL running from a Data extension where I have all marketing opted in contacts, now I need to add a field to the SQL with the device level Opt in and Out options as I will further use these in ...
- 9
0
votes
1
answer
88
views
How to create a priority logic for email in SFMC based on data
Current Use-case:
We have a data extension with the following fields:
CustomerID
Customer_EmailAddress
Customer_Category
1
john@test.com
Category_101
1
john@test.com
Category_202
2
lee@test.com
...
- 173
0
votes
1
answer
41
views
SQL Query Mobile Subscription
Is it possible to query first name, last name, keyword, and mobile number from _MobileSubscriptions?
- 39
0
votes
1
answer
26
views
Clone data from one field to another in a data extension in marketing cloud
I have a Data Extension in Marketing Cloud and I am using Automation Studio to create a querie that copy the data from one field to another.
I want to pass all the event_id data to the new field ...
- 1
0
votes
0
answers
50
views
Any idea why this query keeps timing out?
so this seemingly simple query keeps timing out. There are only ~ 61K records in my filtered DE, though there are about 120 attributes, though most don't have any data. When I run the query without ...
- 19
0
votes
2
answers
81
views
Unable to create temporary data extension: Field name cannot be blank error in Salesforce Marketing cloud
SELECT
ds.Id as [Id],
ds.Discharge_intiated_Date_Time__c as [Discharge Intiated Date Time],
ds.Account__c as [Account__c ],
ds.Opportunity__c as [Opportunity__c ],
op.Appointment_Status__c as [...
0
votes
1
answer
33
views
Running a query to see the comparison of bounce count to # of block bounces and my query is returning 0
Here's my code. I tested the subquery and it returns records. But when I join it it returns nothing for some reason.
SELECT sub.SubscriberKey
,sub.BounceCount
,sub.Status
,bb.BlockBounceCount
,(sub....
- 15
1
vote
1
answer
82
views
Check for existing records in Data Extension when appending data
We're sending anniversary mails to members every time they've lasted another year with us :)
This is the SOQL appending data to the Data Extension [Anniversary Flow - Trigger]
SELECT
ID as 'Subscriber ...
- 531
1
vote
1
answer
40
views
The name of the file resulted from a Mobile Push Data Extract to use in SQL for file transfer
I need some help with understanding how to build an SQL from Mobile Push Extract that needs some transformation to the data like time zone, before being transferred to a AWS bucket.
I have an ...
- 9
0
votes
2
answers
179
views
Error in conversion bigint to varchar SQL
I am getting error SQL Errors occurred while processing retrieved data: Error converting data type varchar to bigint.
This my Query and its come from the where clause. Anyone help me to resolve this ...
0
votes
0
answers
24
views
Analytics SAQL Query Help
I am trying to group by three fields, year, week and type however, I have had to manipulate the original type field to group two of them under the same category. As per the attached image, I would ...
0
votes
1
answer
48
views
Marketing Cloud Query Returning Multiple Records
I have a Marketing Cloud query that returns records from synchronized data extensions. The query is looking for recent appointments scheduled. The query returns duplicate appointments (where the ...
0
votes
2
answers
45
views
Unable to retrieve external key (customer key) for triggered email
I have a go to query retrieve details about my triggered email sends for analytical purposes. It has always worked.
However, for one triggered send in particular the query is returning 0 rows; despite ...
- 140
0
votes
3
answers
205
views
how can I find all fields using SFMC Query Studio
I wanna extract data from data extension but I don't know the fields name exactly.
so I tried select * and select fields(all) but both occurred error.
How can I know all the name of fields in SFMC ...
- 1
0
votes
1
answer
41
views
USING DATEADD but no data export to target de
SELECT A.ContactKey, A.Name, A.Email, A.Phone
FROM customerfield as A
INNER JOIN orderfield as B ON (A.ContactKey = B.ContactKey)
WHERE PurchaseDate <= DATEADD (DAY,-7, CAST(GETDATE() AS DATE))
...
-2
votes
1
answer
97
views
Incorrect syntax near the keyword 'FROM'
I'm trying to activate this this query activity to add it in an automation, I'am facing this error syntaxe : Incorrect syntax near the keyword 'FROM' . I din't build it , I'm juste reusise it. Anybody ...
0
votes
1
answer
84
views
Split into different rows using SSJS or Query or whatever
I'm totally lost spliting my dataset.
The dataset looks like below.
What I want to is split 'Field 3' and build exact like under picture.
Is there any way to achieve this requirement?
If so, please ...
-4
votes
1
answer
72
views
How to add 7 business days from current day in Salesforce Marketing Cloud through Query Activity [closed]
The scenario is to add 7 business days from today (exclude Saturday and Sunday).
Example:
If Today is monday, add 7 business days and my result should be next week Wednesday.
If today is Thursday, add ...
- 3
0
votes
1
answer
95
views
Marketing Cloud Query and SSJS Performance
I have to implement a Marketing Cloud Solution that will have millions of records in a single Data Extension (DE). Most DEs will have 20-30 million records with some DEs going over 100 M. I know how ...
- 143
0
votes
1
answer
100
views
If field is empty populate with the value from another field
I'm trying to write a SQL query to achieve this scenario:
I have two fields Field1 and Field2. Field1 is missing values and I want to write the query when the value from Field1 is missing to get ...
- 429
0
votes
1
answer
81
views
Query to find if a value in one DE column is present in comma separated string from different DE [closed]
I have 2 data extensions, in first DE I have a column called 'Products' (which holds only one product name) against a customer record and there's second DE which has a column called 'ProductRange' ...
- 61
0
votes
1
answer
61
views
Using SQL to get unsubscribed contacts from journey
I am attempting to pull in people who have unsubscribed from a Journey Builder Email Activity.
Is there anything in my query that will cause the following error in query studio?
Query failed during ...
- 140
0
votes
0
answers
53
views
SFMC SQL Transpose Columns to Rows
I have counts against different segments in a DE that is to be picked up daily by Datorama. Incremental increases will be visualized in dashboards. I need to transpose the columns with the counts to ...
- 13
0
votes
2
answers
71
views
sql default value on join
I have a sql query taking everything from one table (sdrcraw) and populating it in a 2nd table. The 2nd table has an additional column called 'local' and this gets populated with a join on country ...
- 69
0
votes
1
answer
67
views
Merging Data from 4 SFMC Data Sources into 1 DE
I'm trying to merge various fields from our synced data sources in SFMC.
For Account_Salesforce, Contact_Salesforce, and Program_Roster__c_Salesforce, the relationship is the account ID, but they are ...
