2

We're making some use of suppression lists and I'm finding that although they seem to live in _ListSubscribers I'm not getting query results when writing SQL to query their ID or Name. Attached is a couple sample queries. What am I missing?

SELECT * FROM _listsubscribers
WHERE 1=1 
AND ListID = '[1111111]'

and...

SELECT * FROM _listsubscribers
WHERE 1=1 
AND ListName = 'My great suppression list'

Data Extension is a carbon copy of _listsubscribers and is formatted as follows:

AddedBy / Number
AddMethod / Text / 17
CreatedDate / Date (Nullable)
DateUnsubscribed / Date (Nullable)
ListID / Number (Nullable)
ListName / Text / 50 (Nullable)
ListType / Text / 16 (Nullable)
Status / Text / 12 (Nullable)
SubscriberID / Number (Nullable)
SubscriberKey / Text / 254 
SubscriberType / Text / 100 (Nullable)

I've also read the documentation and they recommend a slightly different query and DE, listed below, still no results.

Query:

SELECT
SubscriberKey,
EmailAddress,
ListName,
ListType,
Status,
CreatedDate AS DateAdded,
DateUnsubscribed
FROM _ListSubscribers
WHERE 1=1 
AND ListName = 'My great suppression list'

Data Extension:

SubscriberKey / Text / 255 (Key)
EmailAddress / EmailAddress / 254
ListName / Text / 100
ListType / Text / 50 (Nullable)
Status / Text / 25
DateAdded / Date
DateUnsubscribed / Date (Nullable)
2
  • Why are you using where 1=1
    – 0xsegfault
    Feb 21, 2018 at 17:57
  • 1
    1=1 is a basic SQL include so that any statement after can start with an AND or OR statement. Just personal preference, and has no impact on other queries. Feb 21, 2018 at 18:59

1 Answer 1

3

The ListID field in the ListSubscribers system data view is an integer. I would recommend changing the syntax in your where clause from '[1111111]' to 1111111. Also, I would recommend not using the wildcard in you select statement, but specifically call out the column you want to select.

Example:

SELECT AddedBy, AddMethod, CreatedDate, DateUnsubscribed
 , EmailAddress, ListID, ListName, ListType
 , Status, SubscriberID, SubscriberKey, SubscriberType 
FROM _listsubscribers
WHERE ListID = 1111111
1
  • 1
    question: can you confirm your query is completing successfully? I would recommend running in an automation to do this. Feb 21, 2018 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.