ListManager uses SQL queries to determine who should receive a mailing, and to merge in demographic data from tables other than the ListManager members_ table. When building your own SQL queries, please note the following limits to the syntax and commands that may be used.
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, 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. Feel free to join to tables outside of the ListManager provided tables. In fact this is encouraged as the most efficient way to mail merge demographics data. These joins should be made to be efficient by using a unique indexed column like MemberID, so the mail merge logic will allow for high-speed parallel execution of mail merging.
2. The query must be written in SQL-87 syntax, not SQL-92 syntax. For example:
select *
from members_, lists_
where lists_.Name_ = members_.List_
Although the following query works, it is inefficient and may cause poor performance:
select *
from members_
inner join lists_
on lists_.Name_ = members_.List_
3. 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.
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.
4. UNION cannot be used in queries.
5. Dates and times that are not provided in column data need to be able to be parsed by the database server. Microsoft is 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
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.
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. 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).
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.