WHERE Clause and Order By Screen (Magic xpi 4.6)
Use the WHERE Clause and Order By Definition screen to enter a WHERE clause that will be sent to the database in the SELECT statement. The WHERE clause enables you to filter the number of records that will be fetched.
The WHERE Clause and Order By Definition screen contains the following fields:
|
|
|
|
Available Columns
|
A list of all the columns in the tables that you selected. Double-click on a column to add it to the Where Clause Text field.
|
|
Variables
|
A list of variables that you can use. Double-click on a variable to add it to the Where Clause Text field.
|
|
WHERE Clause Text
|
Displays the WHERE clause according to the items that you selected in the Available Columns and Variables fields (above). This text should not exceed 10000 characters.
|
|
ORDER BY
|
Here you define the ORDER BY clause that will be sent to the database in the SELECT statement. The ORDER BY clause enables you to define how you want the results to be sorted.
Click Configure to open the ORDER BY Segments Selection list. This list contains all the available columns belonging to the selected tables. Select the required check box(es), and click OK.
The order that these fields appear in the list is very important to the correct display of the final data, so Magic xpi provides up and down buttons to move the fields within the ORDER BY section.
|
To Use the WHERE Clause and Order By Definition screen:
-
Create a WHERE statement by double-clicking the required Available Columns and Variables. The selected items will be entered in the Where Clause Text field in the order you select them.
-
In the ORDER BY section, select the columns that you want to use to determine the database order.
-
When you have entered the information, click Next. One of the following occurs:
-
If you selected one table in the Select Tables screen, the Summary screen appears.
-
If you selected multiple tables in the Select Tables screen, the Join Statements screen appears.
|
Note:
|
When working with WHERE clauses, you should be aware of the following:
-
You need to add single quotes around selected Alpha variables for Update and Delete operations.
-
To specify a dynamic variable with a value that can be specified in the mapping, show that the database_field=<!?variablename?!>. For example, order: OrderID=<!?MyOrderNumber?!>. This will cause the MyOrderNumber variable to appear in the Data Mapper Destination as part of the WHERE composite element.
-
Only simple WHERE clauses are supported (no nested clauses, etc).
|
How Do I Use Dynamic WHERE Clauses?
