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
17 views

How can I join a Data Extension with the All Subscribers List?

I am trying to create a query that selects the name id, gender, status and Email address from a data extension where the email is different than the email in the All subscribers list. I was thinking ...
0
votes
1answer
37 views

SELECT from _sent dataview - duplicate value error occuring

I am trying to create an Automation to update sent dataview records daily. The code I've tried is the one below, but it kept showing error messages with "Violation of PRIMARY KEY constraint. ...
0
votes
1answer
636 views

Track Email Code snippet included in email, yet no opens are being recorded

I'm using the data view to query opens for the last 30 days. It's been working for the past 2 months. My devs just re-wrote our HTML based email with it's ampscript but the code is still there. I ...
0
votes
1answer
493 views

Birthday Journey with Sql query in Automation

I have created a Journey with Automation for birthday. I have a data extension with birthday field, using which I have created an automation to include only the customer's having birthday's on the ...
0
votes
1answer
29 views

Query for number of Clicks/Open in Journey Emails

I am trying to bring in sql query a number of Clicks and Opens per Subscriberkey in a Journeyname, for example. So far, I did something like bellow, but I couldn't group by Subscriberkey and bring the ...
1
vote
2answers
56 views

Query on Email Content/Email body

Do anyone have any idea how we can query on email body/email content ? I've a use case where I want get all the email name in which I've used a certain number 878XXX784741 and have sent those emails ...
0
votes
1answer
31 views

Count of no clicks on last 3 emails using data views in marketing cloud

I am trying to write a query in SFMC where i want create a data extension with people who have not clicked any emails and we have sent them at least 3 emails. I have the below query but i am getting 0 ...
0
votes
0answers
20 views

Deduping a DE based on 2 fields matching per record

I have a Lead DE that is populated with enquires that come in from multiple brands, so a customer could enquire (these enquiries will have a different key) on more than one brand and on more than one ...
0
votes
1answer
101 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
0answers
20 views

offset is being calculated in sql even after daylight saving ended [duplicate]

Can anyone help me out in the SQL, where the file is coming in UTC and we need to send the SMS in GMT (when no daylight saving) or in BST (when Daylight saving). My SFMC system is in CST timezone. ...
0
votes
0answers
24 views

Marketing Cloud Error on Full Join: Cannot insert a NULL value into a non-nullable column

I am attempting to combine two data extensions in SFMC. Whenever I try to do a full join I get the error "Cannot insert a NULL value into a non-nullable column." If I do a right/left join or ...
0
votes
1answer
46 views

SFMC automation - Archiving only new records from source data extension into an archive data extension [closed]

I want to create an automation which when scans for new records that were inserted into a Data Extension and then copies those records to another Data Extension (archive). The source DE has a ...
0
votes
1answer
53 views

Automation Studio - Violation of primary key constraint, Cannot insert duplicate key

I have an SQL query running in automation studio on a daily basis. It takes data from data extension A and puts it in data extension B. It functioned without any errors for 4 months, but it started to ...
0
votes
1answer
75 views

Can NOT query a subscriber by its subscriber key

I haven't queried data from the _Subscribers data view before and faced with that issue. Please correct me if I did something in a wrong way. At first, I created a temporary data extension with these ...
0
votes
1answer
68 views

Unable to create temporary data extension: Field name cannot contain the character ) convert to date

I'm trying to convert a timestamp data to a "dd/MM/YYYY" format, but I couldn't figure out the correct syntax. I've wrote this query but I'm getting this error message. Unable to create ...
0
votes
1answer
47 views

Splitting CONCAT data in Marketing Cloud [closed]

We are receiving data from our Sales Cloud instance via MCC connector, and in one of the fields tied to a personAccount - "Agent_Names" - we have multiple string values (i.e.support agents' ...
1
vote
1answer
84 views

SQL Query : Using JOINS with CASE Statement [closed]

I've 3 DE MASTER DE with fields SubscriberKey(PK), donottrack, Createddate, Modifieddate Staging DE with field CustomerID(PK), Createddate Reference DE with field donottrack default value false. ...
1
vote
1answer
61 views

How to correctly convert Unix Timestamp to datetime in SQL

I'm trying to get the date format dd/MM/YYYY or anything readable. actually I have the date data with this format stocked in a string field "1620305130000" I have tried the following CONVERT(...
0
votes
0answers
50 views

What am I doing wrong in my SQL query?

I'm setting up a post-purchase journey where consumers will receive an email after purchasing certain items from certain collections. On my first try, I managed to gather this data, however the issue ...
0
votes
1answer
38 views

Remove comma if field is a blank value in concatenation in SQL - Automation Studio

I am trying to concatenate BLOCK_ID1, BLOCK_ID2 and BLOCK_ID3 into one string in BLOCK_ID. I want to remove the last comma in the string. MyDE UNIQUE_ID BLOCK_ID1 BLOCK_ID2 BLOCK_ID3 A 1 2 3 B 1 2 ...
1
vote
2answers
168 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 ...
1
vote
1answer
43 views

MC query dedupes records automatically

I've noticed some quite odd behavior from running queries in automation studio. Below is the query in question: SELECT C.id , CM.id FROM ent.CampignMember_Salesforce CM JOIN ent.Contact_Salesforce C ...
2
votes
2answers
6k views

Query Activity system error in Automation Studio

Why am I getting a system error with this Query Activity in Automation Studio? Select o.AccountID , o.JobID , o.SubscriberKey , o.EventDate , o.isUnique , o‌​.Domain , o.Subscriber‌​ID , o....
0
votes
1answer
43 views

How to get external key (customerkey) from an email trigger?

I want to get all external keys from email triggers in a journey for stopping/publishing/restarting. So far, I can get their JourneyActivityObjectID (TriggeredSendDefinitionObjectID) through the SQL ...
0
votes
1answer
50 views

De-duplicating emails before send

I am trying to write a query to avoid duplicate emails being sent out. I am using contact ID as subscriber key, so multiple contacts can share the same email address across the DB. I do not want to be ...
0
votes
1answer
882 views

How to match journey builder email sends to Job IDs in Data View?

How can I go around matching the job ID of a Journey builder send to the list of Job IDs extracted from the data views in automation studio?
0
votes
1answer
17 views

Possible Bug - Business Unit Subscriber Status Data View has no records?

The BusinessUnitUnsubscribes Data View which is meant to contain the unsubscribe reason has no records. SELECT COUNT(*) AS count FROM [_BusinessUnitUnsubscribes] AS bu Returns 0. Is this data ...
0
votes
1answer
29 views

SQL: Is GETDATE() a deterministic function or not? [closed]

SFMC Limitation: We can't use variables Self Limitation: No usage of SSJS I use SQL Query Activity to get the previous day's records from Midnight to Midnight, just like a Tracking Extract might. e.g. ...
0
votes
1answer
781 views

Using SQL to combine Bounces, Opens, and Clicks into one DE

I currently have two SQLs pushing data into one DE. One populates Opens, and the other populates Clicks. I then combined the SQLs to an automation with the last step being to enter a journey. The ...
0
votes
0answers
12 views

all subscribers per business unit - SQL code data views

I'm trying to get the number of subscribers per business unit using this code so I was wondering is this the right way to do it (using data views in Query studio): SELECT count(a.SubscriberKey) as ...
0
votes
1answer
39 views

New SQL activity for No Clicks or Opens

I have this task to build a new query activity for subscribers who didn't open or clicked in any of the newsletters our company have been sent. The following SQL query is what I've written down, and ...
1
vote
2answers
3k views

Can I use SQL UPDATE Statement in Salesforce Marketing Cloud?

I am trying to update a boolean field of my Data Extension based on the value of some other field. can I use SQL UPDATE statement to do so? Though when I tried writing this query, it gave me an error ...
2
votes
2answers
1k views

How Do I Properly Offset GETDATE() in Marketing Cloud Query Activities for US Eastern Time Year-Round?

I want to use a Query Activity within Automation Studio to find all of our app's users who last logged in between 24 and 25 hours ago. When I run the below query into a data extension, I instead get ...
0
votes
1answer
24 views

Marketing Cloud Report: Count Email Send by Email Name, Month over Month - 1 Years Data

I would like a report that counts the number of emails sent by month, by "email name". Is there a tool that offers these metrics? I know this can be accomplished using SQL joining _sent ...
1
vote
1answer
75 views

How to extract Device opened data in SFMC using SQL Query?

Currently on a daily basis we store email open/clicks etc. data in a data extension in SFMC and then export it to our data warehouse. Example : SELECT O.JobID, O.SubscriberID, O.EventDate, O.IsUnique, ...
0
votes
1answer
35 views

In an automation, I want to query a number from one table and then use it as a parameter for a select top statement in another query

I hope I'm explaining this well enough. I have table/DE a, where column a is strings and column b is numbers. I want to query table/DE a and select a record that has a specific number, and then use ...
3
votes
4answers
22k views

how do i do integration between sql server to salesforce

unable to integration sql server to salesforce I want to integrate sql server to salesforce and update records in sfdc
1
vote
1answer
98 views

Find External Key of Data Extension from JourneyID [closed]

Can we find the External Key of Data Extension related to the Journey Builder using JourneyID? Is this possible to be done by SSJS or SQL?
-2
votes
1answer
35 views

How can I consume a SQL database to create / update records in salesforce using apex? [closed]

I would like to consult you for something, do you know if it is possible that a sql database can be consumed from salesforce? What happens is that account records are loaded into the SQL database and ...
0
votes
1answer
34 views

Compare today's date with date in DE [closed]

I'd like to update my original question, I've figured out how to convert getDate() to today's date. But is there a way to change the field from datetime to date when I do the query? For example: ...
0
votes
1answer
24 views

SQL Query using where with multi option [closed]

im trying to write down a query to filter data in SFMC but it seems there is something wrong please I need help query : Select Email, phone, LastName, FirstName From [sendable] C Where phone = ('...
0
votes
1answer
45 views

Left Exclusion Join when data extension has composite primary key [closed]

I have a query on writing left exclusion joins when the data extensions has the composite primary key. I have below two data extensions DE1 ContactID AccountID attribute sksksksk 111111 six DE 2 ...
0
votes
1answer
63 views

Query Activity - Merging Data Extensions with Different Column Names [closed]

I have a Data Extension called “Merged Data Extension Newsletter” that has fields called “FirstName”, “LastName”, “Email”, and “Id” I am using ID as the Primary Key. I have another Data Extension ...
0
votes
1answer
43 views

SQL Joining Multiple Tables into a Data Extension [closed]

I have two Data Extensions with fields "MID" and "Total". And a third DataExtension with MID and Name. I want to make a NEW DataExtension that has these three tables joined into ...
0
votes
1answer
552 views

Greater / Less than static date

How do I query out a greater than / less than based on a static date? So far I have: (acc.[Most_Recent_Auto_Loan_Application__c] > 2017-08-01 AND acc.[Most_Recent_Auto_Loan_Application__c] < ...
0
votes
1answer
29 views

DATE function to get past 60 days of data

I am running a query to get data from the past 60 days and BEYOND. So basically I am trying to capture anyone whose installdate is Today - 60 days and beyond. And I should be getting data whose ...
0
votes
2answers
612 views

Is there any way to delete subscriber on the basis of certain criteria in Marketing cloud.

Subscribers can be deleted from the lists but what we want to do is to delete the subscriber who has not responded in last 12 months. We have looked into the marketing cloud but couldn't find a way to ...
0
votes
1answer
69 views

SQL Query to find Duplicate email under different ID [closed]

I am trying to run a SQL to get a list of Distinct email ID. For example if the John Does john@somebody.com Jane Doe john@somebody.com Alice J mary@abc.com I want my output to have only Jane Doe ...
0
votes
2answers
1k 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
35 views

I cannot "add" additional column to the OPENED Email Query

I'm trying to pull contacts who opened specific email. I'm beginner so I'm using the query i found in documentation that works but on top of that query I would like to add additional column. Here's ...

1
2 3 4 5
15