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
11
votes
2answers
17k 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
181 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
375 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
108 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
2answers
5k 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....
4
votes
1answer
154 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
49 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 ...
3
votes
3answers
19k 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
375 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
154 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
112 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
2answers
2k 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
1answer
99 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 ...
3
votes
2answers
53 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
628 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
2answers
131 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
2answers
854 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
1answer
37 views

Query association to automation viewable?

Is it possible to see which automation a query activity is part of?
3
votes
1answer
70 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
78 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
173 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
291 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
1k 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
110 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
1answer
135 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
296 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
2answers
280 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
74 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
95 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
2answers
360 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
1answer
417 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
47 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
40 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
536 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
1answer
366 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
3k 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
82 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
1answer
250 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 ...
2
votes
1answer
53 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
328 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
146 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 ...
2
votes
1answer
119 views

Find a count of clicks per subscriber and LinkName

I'm in the process of building a query that will give me a count of all links a subscriber has clicked on with one of three aliases. The possible alias's are "watch", "win", and "play". I've written ...
2
votes
1answer
479 views

Update Data Extension via WSProxy

I've built a CloudPage which runs SQL queries quickly without having to use automation studio. The SSJS that runs the query works perfectly, but I'm also trying to use this function to also update a ...
2
votes
1answer
774 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 ...
2
votes
1answer
211 views

Primary Key Violation where there is no violation

I have a primary key violation occurring where there is no duplicate record within the target extension, nor the source extension (which is a synchronized data extension). Data Action type is ...
2
votes
1answer
618 views

Why is running UNIONS so slow in Marketing Cloud?

I am running a simple select * with three UNIONS across 3 data extensions with only 8 fields each and it takes forever to run, like 5 minutes. Should I make three separate queries instead and run them ...
2
votes
1answer
621 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 ...
2
votes
1answer
46 views

How to parse json using SQL in Marketing Cloud

I'm looking to use SQL, in an Automation, to parse JSON data which is a JSON Array. I have 5M+ rows where I plan to parse the JSON data and write it to another DE where each id value is a separate row....
2
votes
1answer
45 views

SQL for Complaint Data

I'm brand new to SQLs and need to pull a report to show the number of spam complaints our system has and which Job Ids/email sends they are associated with. Would the SQL look like this? SELECT ...
2
votes
2answers
163 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', ...

1
2 3 4 5
12