Creating a parameterized query

Parameterized queries allow you to enter search values - or parameters, when a query is performed. If you often perform the same query then creating a parameterized query will allow you to reuse that query, all you need do is change the values that you enter when the query is run. This reduces the need to constantly create or edit new queries unless the focus of your investigation changes.

Parameters are specified when adding conditions in the Query Parameters dialog by prefixing the value in the Value 1 box with an at (@) sign. Any text that you enter after the at (@) sign will be displayed as the parameter label in the Query Parameters dialog so it is a good idea to give your parameters a meaningful name. This will help when you (or another analyst, if you publish the query) perform the query at a later date.

Note: If you want to perform a standard iBridge query on a value that begins with an at (@) sign and do not want to be prompted for a value each time the query is run, you must prefix the entire value with an additional at (@) sign. For example, if the value in the Value 1 column is @123 then you must enter @@123.

The same parameter name can be applied to one or more fields in your database. When the query is performed you will only be prompted for one value, this value will be applied to each occurrence of the parameter that is defined in the query.

You must enter a value for each of the parameters before the query can be performed. However, if you do not want to enter a value for one or more parameters, for example you may have specified several parameters in your query but you only have values for one of those parameters, you must exclude the parameter from the query by clicking the tick button, the button will change to a cross and you will not be able to enter a value. The parameter will not be used when the query is performed.

This example explains how to use the Query dialog to find all credit card transactions between USA accounts for amounts greater than 20,000 that took place in April using a parameterized query.

This example assumes that the Accounts Example 3 database is already open within the Analyst's Notebook.

In the Query Name box, enter the following name for your query:
USA Credit Card Transactions
In the lower half of the Query dialog, drag two Account icons onto the chart area of the dialog.
Select the Credit Card Transaction link type from the drop-down list and draw a link between the two Account entities.

Click on the Credit Card Transaction link and add the following criteria in the upper half of the dialog in the same way as you added conditions in Setting up query conditions in simple mode:


Field	Operator	Value 1
Date of Transfer	month is	@Transfer Month
Amount	greater than	@Transfer Amount

Note: It is a good idea to give the parameter in the Value 1 box a meaningful name. This name is used as the label in the Query Parameters dialog when the query is performed. This will make it easier to determine the value to enter in the Query Parameters dialog when you (or another analyst) perform the query at a later date.

Select one of the Account icons and add the following criterion in the upper half of the dialog in the same way as you added conditions to the Credit Card Transaction link type.


Field	Operator	Value 1
Country	equal to	@Bank Country

Select the other Account icon and add the same criterion that you added for the first Account icon.
To see the results of your query, click Next. The Query Parameters dialog appears prompting you to enter values for each of the parameters. Enter the following values:
1. In Bank Country enter USA.
  
  Note: There is only one Bank Country box. When you created the query you used the same parameter name for both of the Accounts icons, this means that you will only be prompted to enter one value, the same value will be applied to each occurrence of the parameter that is found in the query.
2. In Transfer Amount enter 20000.
3. In Transfer Month enter April.
Tip: If you are unsure about the value to enter move your mouse over the text box label, this will display a hint that contains the name of the entity to perform the query on, the field to which the condition applies and the operator, for example Credit Card Transaction 1 Amount greater than.

Note: You must enter a value for each of the parameters before the query can be performed. However, if you do not want to enter a value for one or more parameters, for example you may have specified several parameters in your query but you only have values for one of those parameters, you must exclude the parameter from the query by clicking the Tick, this will switch the button to X and you will not be able to enter a value. The parameter will not be used when the query is performed.
Click Finish. The query finds three USA accounts with Credit Card Transaction links between them for more than 20,000 in April.
Select all the results (all check boxes with checks) and click Finish. The accounts and links are placed on the chart.