SQL queries and Aurea List Manager
Aurea List Manager uses SQL queries to determine who should receive a mailing, and to merge in demographic data
from tables other than the Aurea List Manager members_ table. When building your own SQL queries, please note the syntax and commands that may be used.
We recommend that your DBA (Database adminitrator) should tune your SQL query prior to running it through Aurea List Manager. Queries that are very inefficient can lead to performance problems for Aurea List Manager. If the database server is very busy processing an inefficient query and does not have the resources available to respond to queries, Aurea List Manager's performance generally suffers. Inefficient queries directly affects mail sending speeds, and also affect the user interface, as many pages display some database information.
Using SQL queries
The suggestions mentioned below should be reviewed with your DBA.
- Feel free to join
to tables outside of the Aurea List Manager 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 allows for high-speed parallel execution of mail merging.
- 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_
Distinctis already added to segments, so it should not be included in your queries. 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 causes problems.
Note
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, that is queries that return duplicate member IDs causes problems.
- Union cannot be used in queries.
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. Aurea List Manager 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 the United States. Therefore it is recommended to use unambiguous formats whenever possible.
The use of index hints is discouraged, and hints which result in dirty reads should never be used. Aurea List Manager 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.
- The use of stored procedures to create segments is not allowed. Aurea List Manager must be able to modify the SQL to use it for several different purposes.
- The SQL provided cannot use grouping operations like
group byorhaving, and therefore cannot use aggregate operations likesumorcount.
- Cross-list Queries: Queries that span lists are an advanced option allowed by Aurea List Manager. However, its use can lead to confusion. The data for mail merging that is normally be constant, such as the site name, can vary if the segment is allowed to span lists. Aurea List Manager potentially uses 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. Aurea List Manager treats the view as another table and simply executes 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. You may create views and use them as part of segment creation. ListManager treats the view as another table and simply executes the SQL. However, 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.