WHERE Clause and Order By Definition Screen (Magic xpi 3.x)
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.
|
|
Variables
|
A list of variables that you can use.
|
|
Operators
|
Valid SQL operators.
|
|
WHERE Clause Text
|
Displays the WHERE clause according to the items that you selected in the Variables, Available Columns, and Operators fields (above).
|
|
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.
After the New button is clicked, Magic xpi adds a new row to the list. The row contains a combo box with a list of all fields that have been selected. You need to add a row for each field that will be in the ORDER BY clause.
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 arrows 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 Variables, Available Columns, and Operators. 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 column you want to use to determine the database order.
-
When you have entered the information, click Next to display the Wizard Result screen.
|
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?
