0

I would like to use a measure in a SQL request.

Here is my SQL request that works :

SELECT
C.[Civilite] as 'Civilite',
C.[Prenom] as 'Prenom', 
C.[Nom] as 'Nom', 
C.[Email] as 'Email',
C.[SubscriberKey] as 'SubscriberKey',
C.[TopageEmail] as 'TopageEmail',
C.[OptinMail] as 'OptinMail'
FROM [Clients] C
WHERE 
C.[OptinMail]='True'
AND C.[TopageEmail]='False'

And I want to add the measure named "_Total Emails Opened Last 6 Months". But when I write

Left Join [_Total Emails Opened Last 6 Months] S on C.SubscriberKey = S.SubscriberKey

Here is the error message :

An error occurred while checking the query syntax. Errors: [_Total Emails Opened Last 6 Months] is not a known data extension or system data view. You can only query existing data extensions or system data views.

So how my question is : how can I use measure in SQL to filter ?

  • I think you need to post this on Stack Overflow, this does not seem to be a salesforce related issue. – rahul gawale Jun 5 at 14:24
1

You cant.
SQL Queries are designed for Data Extensions and Data Views only. Measures are based on Lists.

The contents of your measure is also available in Data Views.

I hope this helps.

  • Thanks for your comment @DonL ! I will look at it and try to have the idea of _Total Emails Opened Last 6 Monthsis greater than or equal to1 in my SQL – Elodie Jun 5 at 15:08
  • @Elodie - measures are not retrievable inside of SQL. You would use them in Filters in the UI only. Measures are basically a UI version of using a SQL query for those that do not know SQL. – Gortonington Jun 5 at 15:32
  • @Gortonington Thanks for the information ! As per my understanding, filters in the UI are not as much performing than SQL. So I will have to do SQL with Data Views. – Elodie Jun 6 at 12:51
4

Technically you could use a measure in SQL if you wanted to. This would come with the stipulation that you would need to join data from your sendable data extension to the subscriberid/list related data that would be returned from a measure.

  1. Create measure.
  2. Use the measure in a data filter and populate a pre-defined data extension with the results from it that you want.
  3. Use said data extension (created from steps 1/2) with the contents of the measure as it would now be a valid table to query against.

It is also of note that the contents of a measure may not be contained in a data view as they are structured differently from a SQL perspective. Some of the tables that the data views reference would, however, overlap.

I would agree that it would be better to just use the Data view that contained the open/click/etc. data that you were interested in.

1

Some good workarounds have been suggested already. Let me add that there's also plugins like DESelect that offer this through a UI (instead of having to master SQL and Data Views... ;-) ).

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.