SQL Queries and ListManager

There are some limitation of syntax and commands that can be used ListManager's segment bulder. ListManager uses the query you create to determine the recipients of a mailing and to find the demographics information for mail merging (if any demographic columns are requested in the query). Therefore ListManager must modify the query to make changes for each of these uses.

Note: We recommend that your DBA tune your SQL query prior to running it through ListManager. Queries that are very inefficient can lead to performance problems for ListManager. If the database server is very busy processing an inefficient query and doesn't have the resources available to respond to queries quickly ListManager's performance generally will suffer. Inefficient queries will directly affect mail sending speeds, but will also affect the user interface, as many pages display some database information.

1. The query must be written in SQL-87 syntax, not SQL-92 syntax. For example:

select *
from members_
inner join lists_
on lists_.Name_ = members_.List_

cannot be used. Instead the syntax must be:

select *
from members_, lists_
where lists_.Name_ = members_.List_

2. DISTINCT is already added to segments, so should not be included in your queries. Note that this also limits your ability to select some column data types, such as text blobs.

3. UNION cannot be used in queries.

4. Dates and times that are not provided in column data need to be able to be parsed by the database server. Both Microsoft and Postgres are quite good about being able to determine the format of the date/time as the query is processed, but Oracle requires that the user enter the data in a specific format. ListManager sets the format when it connects to Oracle so that all the queries can be standardized. That format is:

YYYY-MM-DD HH24:MI:SS

Postgres and Microsoft SQL Server users should have success providing dates and times in a variety of formats, but do watch out for ambiguities of month and date. A string format of '1-2-2000' could be January 2, 2000, or February 1st, 2000. How this date is interpreted can vary by local custom, so Europe might have a different interpretation of this date than would the United States. Therefore it is recommended to use unambiguous formats whenever possible.

5. Although DISTINCT is added to the query for the purpose of determining the recipient list, you must make their query return only one member-row per member, i.e., queries that return duplicate member IDs will cause problems.

6. The use of index hints is discouraged, and hints which result in dirty reads should never be used. ListManager uses transactions and other logic to ensure the database integrity is maintained. Allowing dirty reads violates this design and can lead to inaccurate results which Lyris cannot support.

7. The use of stored procedures to create segments is not allowed. As noted before, ListManager must be able to modify the SQL to use it for several different purposes.

8. The SQL provided cannot use grouping operations (GROUP BY, HAVING), and therefore cannot use aggregate operations (like SUM or COUNT).

Feel free to join to their tables outside of the ListManager provided tables. In fact this is encouraged as the most efficient way to mail merge demographics data. It is important that these joins be efficient, utilizing a unique indexed column like MemberID, but if the join is efficient, the mail merge logic will allow for high-speed parallel execution of mail merging.

Cross-list Queries

Queries that span lists are an advanced option allowed by ListManager. However, its use can lead to confusion. The data for mail merging that would normally be constant, such as the site name, can vary if the segment is allowed to span lists. ListManager will potentially use the wrong data for that member for mail merging, which can lead to confusion and erroneous results. Therefore it is recommended that this option be used with great care, and with significant testing before used in a production environment.

Views

You may create views and use them as part of segment creation. ListManager will treat the view as another table and will simply execute the SQL. However, keep in mind that it could be possible to create a view with criteria that conflicts with other criteria added to the query by ListManager, resulting in either no rows or a SQL error, so please test before production use.

Views can often lead to very inefficient SQL, so be sure your DBA has reviewed the SQL before attempting to use it in a production environment.