Query the recipient table querying-recipient-table
In this example, we want to recover the names and emails of recipients whose email domain is “orange.co.uk” and who don’t live in London.
-
Which table should we select?
The recipient table (nms:recipient)
-
Fields to be selected as output columns
Email, Name, City and Account number
-
What are the filtering conditions of the recipients?
city and email domain
-
Is a sort configured?
Yes, based on Account number and Last name
To create this example, apply the following steps:
-
Click Tools > Generic query editor… and choose the Recipients (nms:recipient) table. Then click Next.
-
Choose: Last name, First name, Email, City and Account number. These fields are added to Output columns. Then click Next.
-
Sort the columns to display them in the right order. Here we want to sort account numbers in descending order and names in alphabetical order. Then click Next.
-
In the Data filtering window, refine your search: choose Filtering conditions and click Next.
-
The Target element window lets you enter the filter settings.
Define the following filter condition: recipients with an email domain equal to “orange.co.uk”. To do this, choose Email domain (@email) in the Expression column, choose equal to in the Operator column and enter “orange.co.uk” in the Value column.
-
If needed, click the Distribution of values button to view a distribution based on the email domain of prospects. A percentage is available for each email domain in the database. Domains other than “orange.co.uk” are displayed until the filter is applied.
A summary of the query is displayed at the bottom of the window: Email domain equal to ‘orange.co.uk’.
-
Click the Preview to get an idea of the query result: only “orange.co.uk” email domains are displayed.
-
We will now change the query to find contacts not living in London.
Select City (location/@city) in the Expression column, different from as an operator and enter London in the Value column.
-
This will take you to the Data formatting window. Check the column order. Move the “City” column up under the “Account number” column.
Uncheck the “First name” column to remove it from the list.
-
In the Data preview window, click Start the preview of the data. This function calculates the result of the query.
The Column results tab shows the query result in columns.
The result shows all recipients with an “orange.co.uk” email domain who do not live in London. The “First name” column is not shown because it was unchecked during the previous stage. Account numbers are sorted in descending order.
The XML result tab shows the result in XML format.
The Generated SQL queries tab shows the query result in SQL format.
