How Do I Use Dynamic WHERE Clauses? (Magic xpi 3.x)

You can use the Magic xpi Data Mapper service to create SQL statements. There are two types of WHERE clause statements: static and dynamic. This topic explains when and how to use the statements.

	Static WHERE Clause	Dynamic WHERE Clause
Applies to	Source and Destination	Destination (Update and Delete)
When to use	When the value is in a variable.	When the value is not in a variable.
Displayed in Data Mapper screen	No	Yes
Syntax	Table_column = <?Variable_Name?>	table column = <!?Data_holder_name?!>
Data types	Variable can be any data type supported by Magic xpi.	Data holder can be any data type supported by Magic xpi.

Example

The example below shows how to create a dynamic WHERE clause. The steps in the example are divided into these two stages:

• Stage 1 – Source and Destination Definition

• Stage 2 – Data Mapping

The steps assume that your integration project includes a table called exampleTable_Des with the following properties:

Table_Key	Col1	Col2	Col3
Key1	A	56	D
Key2	B	78	E
Key3	C	99	F

It is also assumed that the integration project includes a second table, exampleTable_Sour, with the same structure as exampleTable_Des.

The purpose of this example is to delete records from the exampleTable_Des table, which existed in the exampleTable_Sour table.

Stage 1 – Source and Destination Definition

Defining the Source

1. In the Magic xpi Studio, drag the Data Mapper service to your flow.

2. Click Configuration to open the Source/Destination Management dialog box.

3. In the Source area, create a new Database type entry and click Properties to open the Database Schema Properties dialog box.

4. From the DB Operation list, choose Select and from the Database Definition list, select your database, and click Wizard to open the Database Wizard.

5. In the Select Tables screen, select the exampleTable_Sour table and click Next to open the Select the Columns screen.

6. Select the Table_Key column and click Next to open the Where Clause screen.

7. Click Next to go to the Wizard Result screen and then click Finish to close the wizard and return to the Source/Destination Management dialog box.

Defining the Destination

1. In the Destination area, create a new Database type entry and click Properties to open the Database Schema Properties dialog box.

2. From the DB Operation list, select Delete. From the Database Definition list, select your database and then click Wizard to open the Database Wizard.

3. In the Select Tables screen, select the exampleTable_Des table and then click Next to open the Where Clause screen.

4. From the Available Columns list, double-click the Table_Key column and in the Where Clause Text area add the following: =<!?MyKey?!>. The text should therefore appear as follows: Table_Key=<!?MyKey?!>.

Note: MyKey is a data holder, not a predefined variable.

5. Click Next to go to the Wizard Result screen and then Finish to close the wizard and return to the Source/Destination Management dialog box.

Stage 2 – Mapping the Data

1. In the Source/Destination Management dialog box, click Map to open the Data Mapper screen.

2. Map the Table_Key column to MyKey.

This sets up the deletion of all the records in the exampleTable_Des table, records that are also in the exampleTable_Sour table.

Note: If this example had used a variable instead of mapping the column in the exampleTable_Sour table to the data holder, only one record would have been deleted from the exampleTable_Des table.