Set Up a SQL View

If you wish the data for a transactional mailing populates from an iMIS SQL view, you must create the view from within an iMIS-integrated database and provide a name for that view. One created, the name of the view appears under the Select a SQL View radio button in the Recipient Data File section of the transactional mailing page.

Creating a SQL View in iMIS

A SQL view can be created using more than one table and also any other views in your iMIS database using the proper SELECT statements. 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 are 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. The view 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.