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
12
votes
2answers
23k views

SOQL ORDER BY - 2 Fields?

I'm looking for a way to achieve this in SOQL: https://stackoverflow.com/questions/8532599/need-to-perform-order-by-twice I'd like to order by the Priority field on Case, otherwise order by Age of ...
6
votes
2answers
285 views

Creating Seed DE with Query

I'm trying to create a samples DE of records from the deployment audience, but I'm having trouble making sure all people get all possible values that the email could display. Using the code below I ...
5
votes
2answers
501 views

Allow simultaneous writes to the same Data Extension

I have a use case where I need to bring _PushAddress data from 60 Business Units back to the Global Business Unit. For information _PushAddress has a restricted view of data available in a single ...
5
votes
1answer
335 views

Error appending a Data Extension referenced in query

I have a query in a child business unit with an action type of 'Append.' This query contains a join, and it's targeting one of the Data Extensions referenced in the join. The query was originally ...
4
votes
1answer
121 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 ...
4
votes
1answer
613 views

Marketing Cloud Use SQL Activity to Join Multiple Data Extensions

I've recently completed an IP warming process. Contacts were being added to a new Data Extension before each send to keep track of the the separate imports throughout the migration. Now I would like ...
4
votes
2answers
142 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 ...
4
votes
2answers
1k views

How to Split a string by delimited character and create new rows for every split using SQL query

Hello I have a requirement wherein I have a table row containing a field having value such as ABCDE|12345A|187251D| and so on. The exact length is not known. I want to write a query in SQL to split ...
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
3
votes
2answers
471 views

Ordered Data Extension into 3 ordered segments

Trying to take 3.8MM records and break them up into 3 groups. Group 1 : 0 - 160,000 (already done) Group 2: 160,001 - 960,000 Group 3: 960,001 - 3,610,000 They need to be ordered by when they were ...
3
votes
1answer
204 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 (...
3
votes
1answer
252 views

Automation Studio Query not accepting parentheses in combined AND OR

We've run into a problem which prevents us from combining ANDs and ORs in the WHERE clause of a SQL statement. This is the syntax of our WHERE statement: SELECT s.subscriberKey FROM ...
3
votes
1answer
376 views

error thrown while trying to do a group by

I'm trying to write a query that gives me the name of users and the total number of ids assigned to them. Here I've Daily_Task__c a custom object with a lot of fields, here I'm trying to get the ...
3
votes
2answers
3k views

Query Inner Joins followed by a Left Join

I have a query that I am running that joins a few other data extensions and filters based on Role and Created_At date. Also I need to remove subscribers that are in this group that are also in ...
3
votes
2answers
62 views

Sql Action “Run Once” fails to pull records

I'm trying to run a query that contains a few joins. It is populating a data extension. However, when I "Run Once", not only does it fill the extension with data, but there is no record in the ...
3
votes
2answers
1k 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.
3
votes
1answer
1k views

How do i resolve 'Error while processing square brackets' when there are no square brackets in the query?

this is the query: select opp.* from ( select opp.*, row_number() over (partition by opp.contact_email_address order by opp.status_date desc) as row_number from opportunity_data opp ...
3
votes
2answers
768 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 ...
3
votes
1answer
2k views

Create a data extension with the number of clicks and opened emails

I'm working on a feature for marketing cloud and I need to create a Data Extension with those fields : NumberOfOpenMails,NumberOfClicks and all the other fields relative to the subscriber. I found ...
3
votes
1answer
40 views

Query association to automation viewable?

Is it possible to see which automation a query activity is part of?
3
votes
1answer
81 views

False matches in a SQL query activity

I am trying to automate updates to the All Subscribers list whenever the preferred e-mail in Salesforce changes. I created a SQL query activity that compares our Salesforce data extension to the e-...
3
votes
1answer
122 views

Print reason for SQL query failing in SSJS

I built a Microsite which staff can you use to run one-off queries without having to create an activity and then run it or place it in an automation. It works great but is very basic - when you click ...
3
votes
3answers
245 views

Case copy value from another field into target data extension

I'm trying to write this query and the idea is to look at the field "MessageName" and when that field value is equal to 'Text 1 Response', I want to copy the field value from another field called ...
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....
2
votes
1answer
164 views

SFMC Query Timing Out

I have a query that's assigns engagement definition to subscribers based on their interaction with the email. The dataViewSubscribers DE contains 16million users. However, the query times out now ...
2
votes
2answers
510 views

Optimizing SQL queries

I've set up an automation that resends an altered version of a mail to all who recieved it the first time it was sent, but didn't open it. This is done in Automation Studio using 3 SQL Query and a ...
2
votes
1answer
2k views

SQL Query EXCEPT not functioning as expected

I have 2 tables within SFMC both with SubscriberKey as the only field, which is the primary Key. There are SubscriberKeys that exist in both tables. Test1 has 1,700,000 records and Test 2 has 400,...
2
votes
1answer
207 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?
2
votes
5answers
2k views

Reason why we cannot access data more than 6 months in data views?

Any reason why we cannot access data more than 6 months in data views (in marketing cloud)? why we do not have access to our own data (all time data)?
2
votes
2answers
343 views

Query timing out, but looks right

Why isn't this working? I took this query SELECT s.TriggeredSendCustomerKey as TriggeredSend, COUNT(o.TriggeredSendCustomerKey) as TotalOpens, COUNT(case when o.IsUnique = 1 then 1 end) as ...
2
votes
1answer
829 views

Can i update data extension with auto generated random value

I want to run an automation for adding random value into data extension field. I have data extension with name RecordsDe which having 3 field FieldA, FieldB & PKey. Now i want to add random ...
2
votes
1answer
2k views

Is there a functional difference between _ListSubscribers and _Subscribers with respect to STATUS?

Does anyone know if there is a difference between the STATUS fields for the _ListSubscribers and _Subscribers system tables? From what I can tell the data should be the same, but when I run a query ...
2
votes
1answer
305 views

SQL - Transpose/Pivot Rows to Column

I have a data extension with values like: Email_Address | Job_ID | Date | Etc wmcm@me.com | 1234 | 1/11/19 | etc wmcm@me.com | 5678 | 1/11/19 | etc wmcm@me.com | 9432 | 1/11/19 | ...
2
votes
1answer
111 views

query Open or Click

I'm trying to get the customers who open or click an email and I'm not sure what I'm doing wrong. If I only join the Click or Open, it works but when I do them together, it is timing out which means I'...
2
votes
1answer
557 views

How does Select Distinct work on a Primary Key in Marketing cloud's SQL?

How does Marketing Cloud's SQL in Automation Studio works when having a DISTINCT operator on a data extensions that has a Primary Key (in my case "Customer_Key")? Specifically, does it look for unique ...
2
votes
1answer
2k views

What exactly the “BatchID” is in tracking Data Views?

Lately, I have been trying to figure out the most generic query for getting tracking data into one table. As suggested in the SQL Reference, I have been using this join: ... FROM _Sent s LEFT JOIN ...
2
votes
2answers
627 views

Can i get latest record from data extension having duplicate records

I am updating my target data extension from the data extensions having multiple records by using sql query by union all in automation studio of marketing cloud. my query is SELECT FieldA, ...
2
votes
2answers
255 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 ...
2
votes
1answer
3k views

Query From Shared Data Extension

I have a shared Data Extensions across multiple Business Units. I need to run a query from it in a Child BU but every time I try to run I get "× An error occurred while checking the query syntax....
2
votes
1answer
638 views

Selecting full records, but only 1 of many (if multiple exist)

Say I have a table: ID | Store | Distance 111 | ABC | 0.0249126539935 111 | ABC | 0.0249126539935 111 | DEF | 0.0149126539935 111 | DEF | 0.0149126539935 I am attempting to return: ID | Store | ...
2
votes
1answer
61 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 ...
2
votes
1answer
355 views

Can SQL select values in one table to match a field in another table with wildcards? (Value like %value%)

We have two tables - one is the _click table and the other is a catalog of ProductIDs and we'd like to find all the clicks from the previous day where the ProductID is in the LinkContent field. The ...
2
votes
1answer
580 views

SQL Query valid but Automation to populate Data Extension with engagement data is failing

Attempting to create a 'score' data extension for each email contact, to measure engagement levels with email sends. Background: Aware the contact score function is available on Pardot/CRM, however ...
2
votes
1answer
6k views

SQL Query in Marketing Cloud Today = date

I'm trying to query using a where clause such as this: WHERE Today = a specific date . This is what I have right now: DateAdd(day, 0, GetDate()) = '2018-02-12' (because it was 2/12 when I posted/...
2
votes
2answers
86 views

Help with SQL - want to optimize query for speed [closed]

I'm a SQL neophyte. I've written a query to find records across tables and it works, but I'd like to figure out make it the most efficient for speed. What are some options to make this query more ...
2
votes
2answers
4k views

Pulling data from data views for one business unit only using query

SO, I want to pull data from data views for our business unit only in marketing cloud. Currently, it seems like when I run a query something like: SELECT * FROM ent._Subscribers WITH (NOLOCK) It ...
2
votes
1answer
56 views

Finding MIN(1) date in a data extension - error message about aggregate functions

I'm currently trying to find a list of email Addresses with a particular date in a data extension. The data extension can contain multiple instances of the same email address, but with different dates ...
2
votes
1answer
496 views

Find Subscriberkey based on Subscriber ID

I have a daily file with only Subscriber IDs sent from Audience Studio into Salesforce Marketing Cloud. I have imported the file into a data extension called 'SubscriberID'. I will need to find the ...
2
votes
1answer
876 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 ...
2
votes
1answer
396 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 ...

1
2 3 4 5
15