SQL Views are virtual tables that represent data from multiple database tables and they are a super powerful targeting asset.
Building SQL views in iMIS allows you to use any criteria you choose to create a list. These lists can be used to make very focused and specialized target groups for Informz.
A SQL View is a great alternative to creating an Informz compound target group, in which many different individual target groups are pulled together to make one. Often a single SQL view can be crafted that includes all desired criteria from across different tables and fields to generate a unique list of members with the characteristics you seek to hone in on.
Below is a detailed description of how to set up and use your own SQL to build an Informz SQL View target group.
Creating a View in iMIS
A SQL view can be created using more than one table and also any other views in your iMIS database using a SELECT statement of any complexity. When creating your SELECT statement it is important to know the following:
- COMPUTE or COMPUTE BY clauses cannot be used.
- An ORDER BY clause cannot be used unless there is also a TOP clause in the select list of the SELECT clause.
- References to a temporary table or table variable is not supported. Therefore, the INTO keyword cannot be used.
For detailed descriptions of other restrictions please refer to your Microsoft® SQL Server (TM) or MSDE (TM) documentation.
There are many advantages when using a SQL view as an Informz target group:
- When creating a view, SQL ServerTM validates your SELECT statement against the structure of your database to make sure all database objects referenced in the view exist.
- SQL ServerTM also validates the syntax of your SELECT statement and ensures that all rules for views are being followed.
- You can use any existing table or view from your iMIS database to create your view.
- Once the view has been created, it is extremely easy to create an Informz target group that uses your view.
Informz Rules for iMIS Views
- Creating an iMIS SQL view requires a database administrator (DBA) or a consultant using the SQL Server Enterprise Manager. It is also based on the SQL server and it views in the database. This target group uses the SQL Server Enterprise Manager.
- The only column needed in the SELECT clause of your view is the NAME.ID. Any additional columns are simply ignored by Informz.
- If the column representing the iMIS ID is not named ID, use an AS clause to ensure that the column is named ID. Below are are two examples:
- CREATE VIEW dbo.TestView1 AS SELECT ID FROM NAME WHERE STATUS = 'A'
- CREATE VIEW dbo.TestView2 AS SELECT BT_ID AS ID FROM TRANS WHERE TRANSACTION_DATE > '1/1/2015'
- Informz does not store the View name from iMIS. Informz stores the unique database ID associated with the view. This is stored in the target_group_values field in Informz.
- Only distinct rows can be included in an iMIS SQL.
- The Informz login used during the bridge creation process must have SELECT permissions for any of SQL View to be used by Informz.
Navigate to Subscribers > Target Groups.
Click Create to open the Create Target Group window.
Click the dropdown list and select iMIS SQL Views.
The Add Target Group page opens. Here, enter a target group Name and click the Folder dropdown list to select a folder for the target group.
Click Next to proceed.
Click the Available SQL Views dropdown list and select one of the SQL views to use as the basis for your target group.
Click Update to save the target group.
Understanding Subscriber Counts
The number of records returned from your SQL view may not match your Informz target group. This may be caused by any of the following:
- Only member records that have a valid email address are synced between iMIS and Informz.
- Only member records that match the preferred status values from iMIS will sync between iMIS and Informz.
- Informz target groups never unsubscribed email addresses.
Personalization data is available in Informz mailings from a SQL View. However, it must be entered manually in the mailing story instead of via the Personalization drop down.
The manual code to type is %%IMIS||UD||viewname.fieldname%% where viewname = name of your SQL View and fieldname = name of the column/field you wish to include in the mailing. Personalizations work on a one to one basis. If there is more than one value for a particular iMIS ID, Informz uses the first value returned from the SQL view.
Here are two easy things to check if your SQL View is not returning any counts.
First, ensure that the SELECT permission is being used.
Second, identify an ID that should be returned and verify that this record contains a valid email address and has a preferred status that will allow that data to flow into Informz during a sync.