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
1answer
34 views

Searching for column != values weirdly excludes NULL values in my queries

I have a query in Marketing Cloud which searches for records in my Contact_Salesforce DE. I have a condition where it excludes records if the values are within a certain range, what I've noticed is it ...
0
votes
1answer
24 views

Querying Mobile Push Demographics Table

I'm attempting to query our mobile push demographics table that's tied to the contact record to pull some Application Version data and I'm struggling in Query Studio. The name of the table is ...
0
votes
2answers
39 views

Transpose rows to columns using SQL in SFMC

I want to move data from rows to columns in a SFMC SQL Automation so that we can easily see all the bookings one customer has made, all in one row. We collect bookings on one unique reservation number ...
0
votes
0answers
35 views

Decrypt Field in Data Extension (SQL or SSJS) [closed]

Can we decrypt a field in our data extension using SQL or SSJS, and populate the unencrypted value in another sendable data extension?
0
votes
1answer
17 views

Replace function - SQL - Data Extension

I have a data extension with a column titled 'Email Name'. My reporting I have it pipe (|) delimited into a .txt file. When the 'Email Name' column comes in, some values have spaces in the front for ...
1
vote
0answers
31 views

Marketing cloud System date vs Day light saving (Control via SQL)

I have a landing DE which have a field SystemDate and that is default to Current date. I need to write a SQL which pick data from landing DE and push into Y DE. Our SFMC stack is in Central time zone. ...
0
votes
1answer
26 views

Copy Synchronized Data Extension

I'm looking for a way to mirror a synchronised data extension into a local data extension. My SDE is currently counting +- 6mio records and 100 columns (and yes, we need them!). I feed the local DE ...
0
votes
2answers
33 views

Query Account Data Extension with 2 different keys [closed]

Just ran into a little situation, where it seems the query isn't doing what it's supposed to do. I believe it blocks on the joins and then doesn't proceed to the rest. I didn't get an error, but i'll ...
0
votes
0answers
6 views

How to store Opened Date, Send Date, Clicked Date from _Open, _Click and _Send Data Views in a single column in Data Extension?

I have queried to store the sends, opens, clicks, delivered, bounce and unsubscribed event tracking using a single query. My additional requirement is to store all the Event dates of sends, opens, ...
0
votes
1answer
24 views

Removing surplus characters in a Phone Number column in Marketing Cloud using SQL [closed]

In Marketing Cloud, we receive phone numbers from our CRM in a following format: +3xx yyyyzzzz or 003xx yyyyzzzz. We would need to remove the extra '+' or '00' as well as the extra spacing inserted ...
0
votes
2answers
33 views

SFMC Query Error “An error occurred while checking the query syntax. Errors: Error while parsing query. Please check the syntax”

I am creating this SQL query in Automation Studio. This query gives me an error: An error occurred while checking the query syntax. Errors: Error while parsing query. Please check the syntax When ...
0
votes
1answer
14 views

SOQL Query for Account - Junction - Account

I need to join Account (type = person account) --> Junction Object --> Account (type = business account) We have person accounts enabled on our org. I can easily write what I need in SQL, but ...
-1
votes
3answers
46 views

error message on sql: “Violation of PRIMARY KEY constraint. Cannot insert duplicate key.”

I'm getting this error for the SQL below: Violation of PRIMARY KEY constraint. Cannot insert duplicate key. Select DISTINCT SubscriberKey, Source From ( Select Email, ...
0
votes
0answers
23 views

SQL activity fails in Automation

I have a DE with 5 Primary Key columns. We are trying to insert data into DE by the SQL activity in Automation Studio with UPDATE Data Action. First time the data was loaded correctly, second time ...
0
votes
1answer
24 views

Marketing Cloud SQL Query - Removing Rows containing certain values

So I'm trying to query data extension Data123, and I want to grab all rows where Email_Address doesn't contain 'TEST'. I thought the proper syntax would be Select * from Data123 Where Email_Address ...
0
votes
2answers
52 views

joining two tables [closed]

I'm trying to join two tables(to get the OrderLines) and I'm geetting 0 records even if I manually checked if there is matching records and there is multiple records for each customerID that are ...
0
votes
1answer
35 views

How to avoid null or blank value in target Data extension by using SQL Query [closed]

I have two Data extension in which we need to update some fields from the Data extension "A" to Data extension "B". But if the any fields of A is null or Blank and that fields ...
0
votes
1answer
34 views

marketing cloud - how to join my audience with subscribers having a distinct value [closed]

I have an audience in a table called wtab which contains a column mail, and I want to join it with the subscribers data view without duplicates. when I have one 1 column I can use the DISTINCT keyword ...
0
votes
0answers
36 views

Query a column in a data extension with multiple values

I need to join the ProdutID from this Data Extension with another one from a different DE. Is there a way to do that considering that I have multiple values/fields under one column?
0
votes
1answer
39 views

Update Data Extension with SQL Marketing Cloud

I have a data extension (Name: Factura_T_Alex) with a single primary key (Name: NrFactura) and an attribute (Name: DataEmitere) which is a DateTime type. I need only the date part to use in a script ...
0
votes
1answer
45 views

Help understanding behaviour of dateadd(day,datediff(day,7,GETDATE()),0)

We are using the below code in the WHERE clause of an sql query to return a date of 7 days ago. However, I don't actually understand how it is working. dateadd(day,datediff(day,7,GETDATE()),0) I've ...
1
vote
2answers
90 views

How do I query birthdays coming up in 10 days? [closed]

I'm trying to pull birthdays that are coming up in 1 day. Select ID, Email, Birthdate from Contact_Salesforce contact Where Birthdate = DATEADD(DAY, 1, GETDATE()) I receive 0 results but I know there ...
0
votes
3answers
43 views

How to A/B split a table as part of a query [closed]

Hey guys have a simple use case; I want to take a table and assign 80% of the records a variable of 'A' and the remaining 20% of records a variable of 'B' I know I could easily accomplish this in 2 ...
0
votes
1answer
56 views

SQL query Error Violation of PRIMARY KEY

I run this SQL Query Activity SELECT a.Id, a.Active__c, a.Age__c, a.Email, a.FirstName, a.Salutation, a.PreferredLanguage__c, a.HasOptedOutOfEmail, FROM ent.Contact_Salesforce_43 a LEFT JOIN ent....
0
votes
1answer
32 views

Issue Merging Two DE's with SQL in Marketing Cloud Automation Studio

For some reason I cannot figure out how to merge elements of two DE's into one for use in journey builder using SQL. For reference, I work with a higher ed org, and we're looking to bring in contact ...
0
votes
1answer
32 views

How to put in SQL to see only records that in the field do not have specific data in the marketing cloud?

I am facing a problem when trying to filter the leads and contacts that come from CRM to be used in email marketing. The idea is that the filter does not add people who have the field conta.Name equal ...
0
votes
2answers
76 views

Using a sql query in Marketing Cloud to update a date (birthday) value in the DE to the date this year

I have an automation (running monthly) which adds records into a data extension if their birthday falls within the next month Records are then added into a journey which uses the Birthdate date field ...
0
votes
2answers
56 views

Unopened Query confusion

I am trying to query for unopen email subscribers in the last 180 using the _subscribers and _open data views. Due to the open view being limited to showing only opens and not unopens. I am querying ...
0
votes
1answer
62 views

Why is my RowNumber partition creating duplicate rows on the same information

I need to allow entrants into a journey in Journey Builder multiple times depending on the country of their booking and then preserve the entry and exit dates at a country level. I have created a ...
0
votes
1answer
29 views

Unable to Remove Duplicate records in Query Activity [closed]

I'm using the below query to fetch only the unique "emailid" field value but still I'm getting duplicates SELECT Distinct Emailid , EmailName FROM _job a WHERE a.SuppressTracking=0 and a....
0
votes
1answer
32 views

Query Unsubscribes from a specific MID during a specific Period

I know this code is going to be slightly jacked up... but here it goes... I need a list of people who unsubscribed from a specific MID during 10/1/19 through 3/31/20 Select l.SubscriberKey, l....
0
votes
2answers
68 views

Data Extension Data to JSON with SQL

I'm quite new to SQL and I've hit a road block in my knowledge. I am using Automation Studio to write a SQL query that takes the data from a data extension and transforms it into JSON. The idea will ...
0
votes
1answer
78 views

IN/NOT IN functions not working in Marketing Cloud SQL query Activity

I'm running into this strange issue without being able to understand why it is happening. I want to run this simple query: select a.[Account:PersonContact:Id] from [Salesforce_Data_Event_DE] as a join ...
0
votes
1answer
18 views

Get a one to many database output in one column in a one to one database Salesforce Marketing Cloud

We have a one to many table where we store all our transactions and a table where we store all the seperate items they bought. So it looks like: | **TABLE ONE** | | **TABLE TWO** | | ...
0
votes
0answers
36 views

What is the SQL for linking the _DeviceId field in _PushAddress to the SubscriberKey in _Subscribers

We have configured our App to register for mobile push using the SubscriberKey value that exists in Marketing Cloud. When we view Contact in the Mobile Push channel of All Contacts I can see the ...
0
votes
2answers
66 views

Mass remove records from suppression list

I have a case where I am being requested that any record that has received an email in the last 3 days needs to be suppressed from additional emails. This includes single mass sends, and any journey ...
1
vote
2answers
58 views

How can I get conversion stats via SQL in Marketing Cloud?

I can get this data using Extract Data in Automation: https://help.salesforce.com/articleView?id=mc_as_create_a_data_extract_file.htm&type=0 but I was wondering, If I can get clicks via SQL with ...
0
votes
0answers
34 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
31 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
34 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
31 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
32 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
34 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
43 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
59 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
1answer
49 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
52 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
16 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
45 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
40 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 ...

1
2 3 4 5
13