How Do I Use Dynamic WHERE Clauses? (Magic xpi 4.5)

You can use the Magic xpi Data Mapper utility 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 utility to your flow.

2. Double-click or right-click on the Data Mapper and click Configuration to open the Data Mapper window.

3. From the Toolbox's Mapper Schemas section, drag a Database type into the Source area of the Data Mapper window.

4. In the Database Definition drop-down list, select your database.

5. Click Wizard to open the Database Wizard.

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

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

8. Click Next to go to the Wizard Result screen and then click Finish to close the wizard.

Defining the Destination

1. From the Toolbox's Mapper Schemas section, drag a Database type into the Destination area of the Data Mapper window.

2. In the Database Definition drop-down list, select your database.

3. Click Wizard to open the Database Wizard.

4. In the DB Operation list, choose Delete.

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

6. 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.

7. Click Next to go to the Wizard Result screen and then Finish to close the wizard.

Stage 2 – Mapping the Data

• In the Data Mapper window, 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.