SQL Reference

Considerations

SQL support for the SQL Query Activity is based on, but doesn’t precisely correspond to, SQL Server 2016 capabilities.

• To ensure that queries are executed without blocking from other SQL updates, query activities use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. This is equal to WITH (NOLOCK). Using the NOLOCK query hint is unnecessary.
• Only SELECT statements to data extension or data views in an account or in the parent account
  • Nested Queries
  • UNION
  • JOIN
  • GROUP BY
• Conditional Statements
  • IF Constructs
  • CASE Statements
• Functions
  • Most functions, including MIN, MAX, and more are supported
  • CAST and CONVERT
• Unsupported elements
  • Variables
  • Cursors
  • User-Defined Functions
  • Transaction and Locking
  • GOTO
  • PRINT
  • Any sp_* stored procedure
  • EXEC
  • Temporary Tables and Common Table Expressions
  • TEXT and IMAGE Functions
  • Open-ended comment designations such as '--' to comment out a line

Split data within a column into two rows using the Char function and its ASCII value. Use an ASCII approved character as a delimiter.

To query unicode, it isn’t necessary to append an N to the search string.

Performance Considerations

Queries have a 30-minute timeout. If a query is timing out, it’s possible to make the query perform faster with indexes applied. Queries that include both a join and a Select * statement aren’t permitted. Instead, write a statement that specifies each column name, even when there are multiple columns.

Known Issues

• Data Types
  • Decimal: If a data extension contains a decimal field and that field is used in a query, the output isn’t a decimal output
• Data Views and Enterprise 2.0 Business Units
  • Only data extensions are accessible for queries written at the Enterprise 2.0 business unit
• Data Extensions
  • Don’t set your data extension primary key field for more than 1700 bytes.

Querying Data Extensions

• In an enterprise environment, add the ENT."" prefix to get access to data extension in the enterprise parent account.
• Access a shared data extension in an Enterprise 2.0 account using the ENT. prefix.
• Use the data extension Name in SQL queries, not the External Key.

Querying Subscriber Data

Using the _Subscriber Data View

SELECT SubscriberID, EmailAddress, Status
FROM _subscribers

Using the _EnterpriseAttribute Data View

SELECT _SubsriberID
FROM _EnterpriseAttribute

Note This query can be used in Enterprise 2.0 edition accounts only. Core, Advanced, or Enterprise don’t support querying subscriber attributes.

Combining the _EnterpriseAttribute Data View and the _Subscriber Data View

SELECT
b.[first name],
b.[last name],
b.[age],
a.[Status] as 'Subscription Status',
a.[EmailAddress] as 'Email'
FROM
_subscribers a
INNER JOIN
_EnterpriseAttribute b
ON
a.subscriberID = b._subscriberID

This example assumes the [first name],[last name], and [age] attributes exist in the account.

Sample Queries

GetaSend List Based on Aggregate Row Count

Use Case: Generating a Sendable list from a detailed data set

SELECT visitorid FROM [cart abandonment segment] GROUP BY visitorid HAVING count(visitorid) > 1

Random Sample of 33 Percent Exclusion

Use Case: A/B split testing and general segmentation

SELECT TOP 33 PERCENT

offerid,score,name,description,url

FROM Offers

inner join Offer_Ext1 a on a.offerid <> offers.offerid
order by newid()

Random Sample of 33 Percent

Use Case: A/B split testing and general segmentation

SELECT TOP 33 PERCENT

offerid,score,name,description,url

FROM Offers
ORDER BY newid()

Random Sample of 100

Use Case: A/B split testing and general segmentation

SELECT TOP 100

offerid,score,name,description,url

FROM Offers
ORDER BY newid()

Unique Values for a Data Set

Use Case: Testing

SELECT DISTINCT
'109406145' as linkage_id,
'109406145' as ticket_id,
'28480048' as family_id,
'73334' as merchant_id,
'25.32' as Original_Transaction_Amt,
transaction_type_cd,
Max(TRANSACTION_DATE) as Transaction_Date,
TRANSACTION_STATUS,
MERCHANT_TYPE,
'0' as Group_Points,
'.00' as Group_Comp,
'.00' as Group_Credit,
'.00' as Group_Dues,
'0' as Group_Miles,
'.00' as Group_BonusComp,
'102' as Group_BonusMiles,
'0' as Group_BonusPoints,
'.00' as Group_BonusCrdt,
'.00' as Total_Comp_Earned,
'.00' as Total_Credit_Earned, 
'10' as Total_Miles_Earned,
'103' as Total_Points_Earned,
MERCHANT_CATEGORY,
RA_SEQUENCE,
'FFAK' as fam_campaign_cd

FROM Subscriber_Activity

GROUP BY transaction_type_cd,  TRANSACTION_STATUS, MERCHANT_TYPE,  RA_SEQUENCE,  MERCHANT_CATEGORY

Geo-Targeting

Prerequisites: Zip Code Data Extension. Search for current ZIP code data on the web. Use Case: Find all cities or zips, and therefore subscribers, within 15 kilometers of a ZIP code. -- 6378.137 earth circumference

SELECT   
  city,   
  zip,   
  ROUND(6378.137 * ACOS(     
    CASE       
      WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254)))) > 1 THEN 1       
      WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254)))) < -1 THEN -1       
      ELSE (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254))))     
  END),0) AS Distance 
FROM   
  [ZipCode] AS geo 
WHERE   
  ROUND(6378.137 * ACOS(     
    CASE       
      WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254)))) > 1 THEN 1       
      WHEN (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254)))) < -1 THEN -1         
      ELSE (SIN(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [ZipCode] WHERE zip = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [ZipCode] WHERE zip = 46254))))     
    END),0) <= 15

Pulling Tracking Information for a Triggered Send

Use Case: Viewing available tracking information regarding a triggered send

SELECT SubscriberID, EventDate
FROM _Open 
WHERE TriggeredSendCustomerKey = 'External Key of Triggered Send'

Retrieve Deactivated Mobile Numbers

Use Case: To ensure that the data pull matches the Carrier Deactivation SMS Summary, retrieve the deactivated (opted out) mobile numbers.

select 
LogDate,
SubscriberKey,
MobileNumber,
OptOutStatusID,
OptOutMethodID,
OptOutDate,
OptInStatusID,
OptInMethodID,
OptInDate,
CreatedDate,
ModifiedDate 
FROM _SMSSubscriptionLog
WHERE OptOutMethodID = '20' 
AND OptOutStatusID = '1'
AND CreatedDate >= getdate()-30