Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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)

3
votes
2answers
181 views

Can I define two primary keys in a database table & why?

In relational databases we can only define one primary key. Is it possible to define two primary keys, probably one is primary and other is secondary key and can be joined on any of the two fields.
0
votes
0answers
35 views

How do I call SQL Server stored procedures from Salesforce? [on hold]

I've been asked to build a Build SSIS packages and deploy/execute them from Stored Procedure. This Stored Procedure should be executed/called from Salesforce. I've built the SSIS packages and deploy ...
1
vote
1answer
21 views

SFMC Data View Query

I have a query, I want to bring in all the Sends (for particular email) and opens. But in my query it only brings the matching Opens. For example email sent to 1000 subscribers and total unique opens ...
0
votes
2answers
45 views

Query Contacts that Exit Journey

Is there a way to query Marketing Cloud to show contacts who have exited a journey? Thanks in advance.
0
votes
1answer
37 views

Will Update SQL In Automation Studio Remove Records That Don't Satisfy Requirement?

I am attempting to automate our payment failures to alert consumers their subscription payment hasn't processed. My initial thought was to run a SQL activity once a day and inject new records into a ...
0
votes
1answer
16 views

Creating an Automation for Unsubscribe using Automation Studio- Marketing Cloud

I am trying to create an automation process to filter out my least active subscribers and then unsubscribe them from my communications (my subscribers are saved as a salesforce data extension). I was ...
0
votes
1answer
44 views

Use measure in SQL

I would like to use a measure in a SQL request. Here is my SQL request that works : SELECT C.[Civilite] as 'Civilite', C.[Prenom] as 'Prenom', C.[Nom] as 'Nom', C.[Email] as 'Email', C.[...
0
votes
1answer
41 views

Alternative for Contains when writing SQL for Marketing Cloud

Is there a way to write a SQL query that looks in a Data Extension field for a contains X value I have a Data Extension that has a Description field and i want to write a query that extracts any ...
0
votes
2answers
26 views

Query with configurable column as where condition

I am trying to write a SQL query in automation studio but one column in where clause should be variable or configurable. Means, we should not hard code the value for a particular column and we should ...
1
vote
1answer
22 views

Get list of contacts associated with an opportunity

I created a ruby on rails service that emails a list of contacts when an opportunity is closed. To do this, I am trying to get all contacts associated with the Opportunity that was just closed. ...
0
votes
0answers
42 views

Pre-Birthday Selection

I´d like to send a pre-birthday mail 1 week before the birthday. Therefore I need the SQL statement. Right now it looks like that: SELECT * FROM MasterAbonnenten WHERE DAY([Birthday]) = DAY(...
0
votes
2answers
23 views

How to get email address against email id in Job data view?

Job data view provides the status of an email if it was delivered or not but it provides only Job Id and Email Id. This email id is a numeric number and not the email address. How to get Email ...
1
vote
1answer
46 views

Marketing Cloud COUNT and Group by specific value

I'm trying to create a SQL Query which counts the same values in a Data extensions and Groups them by a Unique Value. Usecase: My data extension looks like this: I want to see how many email ...
0
votes
1answer
52 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
1answer
34 views

Find email address that are part of a domain exclusion data extension

I'm looking to find all email addresses in our database where the email address domain is included in one of our domain exclusion data extension. I thought of using something like the below, but it ...
1
vote
0answers
53 views

SSJS script activity not updating Query Definition as expected

I am trying to update a query definition in Marketing cloud using SSJS script which updates the query based on the datestamp. When I run the script, I don't get any error but it does not seem to be ...
0
votes
1answer
33 views

Is it possible to have a DE that houses all click-related information to be used in a journey?

I want to be able to trigger activities and journeys based on Click behaviours, but from what I understand, to do this I would need to create a SQL query, a Data Extension, automation, and a DE ...
0
votes
0answers
32 views

Contacts with multiple email addresses tied to single ID - SQL Join

We have 2 data extensions which we would like to join. The first called "MasterContacts" has contact information, including multiple email addresses tied to one FederationID, which is present in the ...
0
votes
1answer
51 views

Issue with SQL query [closed]

I'm having an issue with my SQL in marketing cloud to fusion 2 data extensions : Select contacts.Contact_ID contacts.Societe_ID contacts.Mail demandes.Demande_id demandes.Societe_id demandes....
0
votes
1answer
27 views

Marketing Cloud - SQl logic

I am writing a bunch of queries to update All Subscribers list, and was surprised to see that when I put the condition xxx != yyy it only works if there are values in both columns. I was hoping that ...
0
votes
1answer
37 views

Marketing Cloud SQL If Statement

I want to assign a Yes or No to a column based on if another variable is Blank or not. Would it be something like this? if OpenedDate is Empty then set Opened = No ...
2
votes
2answers
61 views

Marketing Cloud SQL Query - Show All Send Stats by Subscriber Across All Journeys

Just like the below SQL to see who bounced in Journey Send Emails, can expand this to include everyone? If they've bounced or not? select s.JobID, s.ListID, s.BatchID, s.EventDate as 'SendTime', ...
0
votes
0answers
54 views

'Order By' in SQL Function now working and get error [duplicate]

I have a Data Extension that is populated by SQL Activity with the code below: Select DATEADD(hour,4,GETDATE()) as Hour , Convert(date, getdate()) as Date , Count(*) as Records FROM Customers ...
0
votes
2answers
35 views

Adding filter to INNER JOIN with 3 tables

I would like to perform a SQL in Marketing Cloud on 3 data extensions, but for each join I would like to create a filter (a WHERE). I'm worried about the positioning because the WHERE clause would ...
1
vote
2answers
35 views

Getting few records with subscriberid in _subscribers

Can you please advise what is wrong on my query where I am getting very few records with subscriberid coming from _subscribers dataview. SELECT EmailAddress as 'EMAIL_ADDR' , SubscriberID , Status ...
1
vote
1answer
42 views

Query failed during execution | Error: Violation of PRIMARY KEY constraint

I have SQL Activy below and some days ago happens the errored: Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Clientes_RDB'. The ...
0
votes
1answer
31 views

ISO8601 Z conversion to local date (Pacific) within SQL

I can't figure out how to take this ISO8601 date and convert it to local time (PDT). I've tried a couple things: convert(datetime, updated_at_text, yyyy-mm-ddThh:mi:ss.mmmZ) as LastUpdated, convert(...
1
vote
0answers
38 views

How do I order rows in columns using AutomationStudio SQL

I am performing a SQL query in Automation Studio and I want the following: Populate a data extension with ID of the Account_salesforce table with the ListName (Lists) bound to the ID of the first ...
1
vote
1answer
44 views

One-to-many in SQL joins

we would like to join 2 data extensions in Marketing Cloud based on the 1-to-many relationship. To make it simple, among other attributes, the first DE contains the FederationID, as PrimaryKey, and ...
1
vote
2answers
51 views

Marketing Cloud SQL not executing

In Marketing Cloud's Automation Studio, we keep getting an error for SQL we wrote that is supposed to combine the data from 2 separate SHARED data extensions in a child Business Unit. The error ...
1
vote
1answer
42 views

SQL query to link email open date & last purchase date / Marketing Cloud

Thank you for your response. The error message is this one : " Automation failed due to system error."
0
votes
1answer
23 views

Importing data with primary key duplicates

We have a file that's dropped on the FTP regularly to update our master database, which has FEDId as its primary key. On rare occasions, the receiving file can contain a few records with identical ...
0
votes
0answers
55 views

How to query “Last Application Open” & “Last Send” from _PushAddress or other relevant data view?

I can query the system attributes from the _PushAddress data view as listed here: Attributes in _PushAddress and _PushTag Data Views But I am unable to fetch the "Last Application Open" & "Last ...
1
vote
0answers
46 views

Data Views unique subscriber clicks

I've created a data views report for capturing clicks across my subscribers and I'm having issues with returning subscribers multiple times for different emails but with the same links and click event ...
0
votes
1answer
59 views

Need help in SQL query [closed]

I have two tables as follows: Table A ------------------------------------- TotalClicks Market__c JourneyName 7 Tampa J1 4 Dallas J1 21 Tampa J2 ...
0
votes
1answer
43 views

SQL Query on _Sent Data View for Suppression List (5 sends in 7 days)

I'm working in a new org where we don't have any send data. I need to set up a suppression list for any subscriber that received 5 emails in the last 7 days. I am not 100% sure if my code below will ...
1
vote
1answer
64 views

SQL Query Activity Data Extension Values are Case Sensitive

Are Data Extension values case sensitive when querying with SQL query activity? I found this to be the case when querying WHERE Status = 'Active' that it ignored records with a status value of "...
1
vote
1answer
41 views

How to update a field in a DE using sql?

I have a DE where i have 3 fields(cost price , selling price and on sale).If the selling price>cost price...then on sale field should be updated as "True"(on sale is boolean field.).What is the query ...
1
vote
1answer
27 views

Geolocation Query [closed]

I have two lists within SFMC that both have latitude and longitude values. I need to be able to compare them and find the distance between them in miles, then include or exclude records based on the ...
1
vote
1answer
62 views

SQL code unknown

I found a piece of code in our cloud that was written by someone in the past, and I don't know what it does (I have no developer experience). %%[ if Not Empty(@zip) Set @rows = LookupRows(...
1
vote
1answer
49 views

Using SQL to compare and update data extension

I need to use SQL in an automation to compare the data from the "TemporaryDE" with the Data from the "CompareDe". If the CompanyName value from the "TemporaryDE" is not present in the "CompareDe" - to ...
2
votes
1answer
47 views

Marketing cloud sql query execution error - Violation of PRIMARY KEY constraint

I'm getting following error during update activity. Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Enq_opp_Parallel_ContactView'. The ...
1
vote
1answer
37 views

Marketing Data Extension `Last Click Date` from data views

I have a query that's intended to update a field named Last_Click_Date based on the most recent EventDate from the _Click data view. It doesn't appear to be grabbing the most recent date param and ...
3
votes
1answer
37 views

Query association to automation viewable?

Is it possible to see which automation a query activity is part of?
1
vote
1answer
51 views

Marketing Cloud SQL Query 1 to many only get result if only 1 record is present in that Data Extension

I know a thing or two about SQL Queries, but in this case i can't figure out what to do. So in this case we have a Contact_Salesforce DE where all contacts are in. We have another DE named ...
3
votes
2answers
54 views

Remove characters after @ in email address and replace with new domain

I have four brands that all use different email domains however our SF only has one email allocated per user with a random branded domain. eg usera@brand-a.com also uses @brand-b.com, @brand-c.com and ...
0
votes
1answer
23 views

Merging Content from the same field across multiple records through SQL in Salesforce Marketing Cloud

I have a DE that collects information about past reservations for travel. One of the fields is named FareDiscountCode. I currently have in place a query from that table that combines all of the ...
0
votes
1answer
28 views

Filter in a 1:many relationship for one value only

we have two Data Extensions - DE1 one storing client's data and another one, DE2, storing information on with a 1: many relationship. We now need to filter on exactly one of the values DE2 Data. ...
0
votes
2answers
77 views

SQL How to create a separate record for comma delimited values in within a field

I have a table which have two fields ID and Source. Source col. have multiple emails with comma separated. I want to create a separate record for each email with same ID. So data looks like this ID ...
1
vote
0answers
128 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 ...