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

transact-sql seems is outdated and not anymore being supported

As soon as I opened the official resource for Transact-SQL from the link provided on the official support page (bottom links), a notice from outdated appears at the top of the Microsoft page. This ...
0
votes
0answers
11 views

Append in query keeps re-adding same data

Below is my query, I have append selected in my automation and insert it into an FTP. I am querying data views. The problem is the same records keep being added every file creation (%%Year%%%%Month%%...
0
votes
0answers
31 views

Error while parsing query: SQL validates in another order

I keep coming across this error message in certain queries and have found that if I move certain elements of the query (without changing anything else), it validates fine. My latest example is below. ...
0
votes
1answer
29 views

link CustomerID with Order Date for every product SKU

I'm trying to link some tables to create an audience for a campaign but the way I link them doesn't give me the right audience. When I check from the last week, I get many more products over £1000 ...
0
votes
1answer
54 views

Record not in unsubscribe data view

When I query _Subscribers I can see a record with a status "unsubscribed" There is nothing related in the _Unsubscribe data view though (I've checked all Business units in the multi org structure) ...
0
votes
1answer
37 views

Create an automation to update Test accounts Status from “Unsubscribed” to “Active” [closed]

I am sure this is a thing, but we have a group of "test" email accounts that our QA group regularly tests. Of course the unsubscribe is one of those things they test, and often we have to reset the ...
0
votes
1answer
61 views

convert text field to number

I have a data extension with a text field that contains numbers like 25.00, 130.00. I'm trying to convert that to a number field so I can write a condition number greater then but because its a text ...
0
votes
1answer
33 views

If constructor in SFMC query

How to do if conditioning within SQL statements SELECT su.emailAddress, IF C.SubscriberKey IS alphanumeric THEN '' ELSE C.SubscriberKey AS SUBSCRIBERKEY FROM from [_CLICK] C join [_SUBSCRIBERS] su on ...
0
votes
1answer
21 views

Combine 2 Booking Tables [closed]

Simple question - customer has asked to combine 2 sets of booking data. The 2 sets are from different types of bookings: BookingA Table Fields: Booking_Arr_Date Booking_Date Booking_Status ...
0
votes
1answer
24 views

Case when in Query studio

I am trying to count customer and non-customer in my master-DE. Having a customerID means he is an customer. SELECT SUM(CASE WHEN a.customerID is not null THEN 1 ELSE 0 END) AS 'Customer', SUM(CASE ...
0
votes
1answer
44 views

Customer and Bookings DE - Save COUNT of Bookings in Customer DE?

I have 2 De: XYZ Customers XYZ Bookings Within both DE there is an email field with the below names: XYZ Customers email field is called 'EmailAddress' XYZ Bookings email field is called '...
0
votes
1answer
40 views

ORDER BY Event date when making an sql query inside MC

https://help.salesforce.com/articleView?id=mc_as_data_view_click.htm&type=0 Each view has some type of event-date creation. When creating my automation and appending the SQL query, I want use ...
0
votes
1answer
40 views

Date add unexpected results

This is my query: SELECT FirstName FROM Account_Salesforce WHERE (dateadd(year, (2020 - year(PersonBirthdate)), PersonBirthdate) >= dateadd(day, 7, GetDate()) AND dateadd(year, (2020 - year(...
3
votes
1answer
96 views

SQL Query timing out - Optimization Help?

I'm trying to run this as a query activity (writing to a data extension) but am finding that it's timing out. It's not all that 'heavy' of a query but it's not completing. I'm a database developer by ...
0
votes
1answer
68 views

Why is job's EmailName empty after being retrieved via SQL when it certainly has a value?

Below query is not capturing EmailName. Any idea whats wrong here? select c.AccountID, c.OYBAccountID, c.JobID, c.ListID, c.BatchID, c.SubscriberID, c.SubscriberKey, c.EventDate, c.Domain, c.URL, ...
0
votes
1answer
50 views

Query to find all the subscribers from a child business unit who joined during a specific time frame

I am trying to query all the subscriber from a child business unit who joined during a specific time frame. Here's my SQL below Select s.SubscriberKey, s.EmailAddress, s.DateJoined ...
0
votes
0answers
38 views

I would like to give value to a field with values ​from a field in another table

I have two data extension, in the first one I have an empty field called codigo, to which I would like to give values ​​that are stored in the second data extension in the IdCodigo field, with the ...
0
votes
2answers
53 views

Query to find date of subscriber's latest email

I'm trying to build a series of queries to keep a definitive list of all subscribers, along with the date of their latest email from us. I've almost got it working but not quite. The sequence is as ...
0
votes
1answer
43 views

Performing an Group By Function in SQL in Marketing Cloud Activity [closed]

I'm trying to perform a Group By function on my Click data view Data Extension, which was built from the _Click data view. (Not sure if it's necessary to have the extra layer.) I am looking to ...
0
votes
1answer
43 views

Updating a DE with no Primary Keys

This might be a stupid question but is there a way to update records (yes many records and duplicate ones) in a DE without a primary key? I am not the owner of the DE which is already structured for ...
2
votes
1answer
46 views

SQL Query to get CustomerID from two DE

I'm looking to write a SQL Query to get the CustomerID from Pot A and Pot B with a distinguish label so I can see that CustomerID 123 comes from A and CustomerID 234 comes from B. I have the ...
0
votes
3answers
50 views

How to write SQL query to pull only not matching Email IDs

I need to write a query to pull records which Email IDs are not matching in another table. For example, I have a two tables whose structures looks something like this: Table1 ID | Name | ...
0
votes
0answers
51 views

SQL Query activity Order by function [duplicate]

I want to order the result of my query result and put it via Query activity into a data extension (DE has no primary key). The order by MAWE is pretty important for me. select count (SubscriberKey) ...
0
votes
1answer
66 views

DISTINCT is not working as expected in Marketing Cloud

I'm experiencing problems with adding distinct in SQL automation. With SQL shown below I still receive duplicate clientids. Primary key would not work for me since during the next run of this ...
0
votes
2answers
47 views

Get subscribers with a changed status during last X days

We would like to send a list of subscribers' status changes both from "Active" to "Unsubscribed" and vice versa to SFTP. We would like to run this activity two or three times an hour to imitate real-...
4
votes
2answers
47 views

SQL Query on reverting data from DE based on a filter condition and modify it before i put it into another DE

I am trying to retrieve multiple Columns from a DE and split a column into multiple columns and insert into another DE along with the original column. Ex : I have a DE-1 with Full name, Email and ...
0
votes
1answer
33 views

Contacts and Orders Join - Primary Key Violation

I'm trying to pull in a list of contacts who've ordered within the last 2 days into a separate DE with the contact ID as the Primary Key. However, I've tried several SQL Join queries in Marketing ...
1
vote
1answer
52 views

Data View queries optimization

Some simple Queries joining together some Email Data Views are timing out and I'm having a bit of a hard time understanding why. They're simple and concrete enough.. Any ideas to tackle this would be ...
0
votes
0answers
19 views

Repercussions of adding new field to journey entry data extension

I'd like to add a nullable field to a data extension that's used as an entry source for an active journey. I know this is possible, but what are the repercussions of adding the nullable field if the ...
0
votes
2answers
68 views

Date 14 days before current date

I have a subscription program and I want to create a journey and send an email 14 days before the subscription program ends. In order to do that I need to run a query to get only the customers whose ...
0
votes
1answer
56 views

Unique opens and clicks from Child BU for child and parent account for past 1 year not working

I have tried basic SQL which is on help document to get last 30 days opens is not working. here is what i have Select j.JobID, j.EmailName, j.DeliveredTime as SendTime, o.EventDate as OpenTime, s....
1
vote
1answer
138 views

Marketing Cloud Query - Failure: Cannot Insert a NULL value into a non-nullable column

I've got a target DE with the following data privileges: ContactId = PRIMARY KEY Email = Nullable FirstName = Nullable HomeStore = Nullable State = Nullable I am currently running this query and am ...
-1
votes
1answer
47 views

IF NULL SQL returning error

I want a SQL query to return "VIDE" when the field is empty in Sales Cloud but I have an error message saying "Incorrect syntax near the keyword 'IF'. Here is my SQL part : IF NULL (...
0
votes
0answers
25 views

Composite Key aggregate function group by clause

I'm trying to find out how many emails have been sent to a customer in the last 10 days. I have a Data Extension with a composite Key, the number of emails recieved(CustomerID) and the number of email ...
0
votes
2answers
45 views

Unique Openers By Each Month

I have a query which pulls information about unique openers in the last 30 days, but I would like to find this info for each month from the last 6 months. Here's the query I wrote Select o....
1
vote
1answer
124 views

Getting duplicates from a data extension

We would need to extract duplicate values from our Marketing Cloud data extension and deliver them to our data team for review. The DE has about 34k contacts and only 3 fields: Account_ID, ...
2
votes
1answer
105 views

Using SQL query in Marketing Cloud with a hidden field in Sales Cloud

Is it possible to run a SQL query that uses a hidden field in Sales Cloud?
3
votes
1answer
148 views

An error occurred while checking the query syntax. Errors: Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax

I've had a few different kinds of query return the following error message, quite often when I'm using 'case when': "An error occurred while checking the query syntax. Errors: Old style JOIN (...
0
votes
1answer
75 views

How to select only the characters appearing before a specific symbol in a SQL Select statement

I have strings in a database like this: firstname.lastname@domain.com And I only need the characters that appear before the @ symbol. I am trying to find a simple way to do this in SQL.
0
votes
1answer
42 views

Getting all sends, and (unique) opens and clicks into one Data Extension

We would like to get the sends, (unique) opens and (unique) clicks from last 24 hours in the same data extension. I started with 2 LEFT JOINS, with the goal of getting all sends, and opens and clicks ...
0
votes
1answer
156 views

Marketing Cloud SQL - Invalid Column Name

After I added the Group by statement I got an error that now is saying the column name is invalid, but it does match with the table. Any other ideas of what might be causing the error? ERROR: An ...
0
votes
0answers
81 views

Dataclip Error ERROR: cannot execute CREATE TABLE in a read-only transaction

I just created a Heroku database but when I try and run a simple create table command in the I get the data clips it says: Dataclip Error ERROR: cannot execute CREATE TABLE in a read-only ...
0
votes
1answer
48 views

Identify records in data extension from CSV file [closed]

I am trying to identify a series of records in a data extension. I have a CSV file containing all the records that I want to identify in ly data extension but I don't know to proceed to clearly ...
1
vote
0answers
13 views

One automation with 2 SQLs triggering 2 separate journeys [closed]

We want to design an automation in Automation Studio that would comprise 2 SQLs. Both queries will populate data into separate data extensions, which we would like to use in 2 separate journeys as ...
0
votes
1answer
39 views

Deduplication based on non primary key field

I have a data extension that has duplicate email address and it has a primary key field. I need to deduplicate the data extension based on the email address. The rows that are duplicate have same ...
0
votes
3answers
97 views

Marketing cloud SQL Query Deduplication

I am trying to deduplicate my entry data extension based on the completeness of field values. For Instance, i have the a data extension with same email address, the record that has maximum field ...
0
votes
0answers
138 views

SQL - Could not convert date and/or time from string data type

What I have: A source data extension with slightly over 14000 records Two attributes in the DE: GlobalID (Text 10) BirthDay (Text 8, YEARMONTHDAY, no dashes) The target data extension has ...
0
votes
1answer
119 views

Find active subscribers for each month for last 6 months

I am trying to find out the number of active subscribers who opened at least one email in the last 30 days, I would like to find this info for last 6 or 12 months, to monitor the growth of active ...
0
votes
3answers
192 views

SQL query: not working properly for blank values

I have an issue, where SQL queries do not work correctly for blank values. A very brief summary: I have TABLE A with 200k rows (no duplicates). In this table there is field called MailingCountry. ...
0
votes
1answer
30 views

Marketing Cloud- Extract click activity from a Cloud Page

We created a custom landing page with a custom form, which we used to collect customer orders stored in a data extension. The possible choices (type: radio button) were 30, 60 or 90. The subscribers ...

1
2
3 4 5
12