3.7.1 The Operation Area
Use the options in this area to specify your selection requirements.
Selected fields
Select the field to which you wish to attach a search condition (i.e. an Operator). Take care when choosing this field, as choosing the right type of field can really affect the performance of your query.
For example, by selecting a key field instead of a data field, your query can run up to 10 times faster. You can recognize key fields, as they have a key icon in the Query window.
More than one search condition can be added to a field, although each condition must be defined separately.
Note: Do not select a virtual field here. If you add a search condition to a virtual field, it will degrade the performance of your query. (The icons of virtual fields are colored blue in the Query window.)
Operators
Select one of the operators from the drop down list. You can select:
|
Equal To Not Equal To Like Is In (Contains) |
Less Than Less Than or Equal To Greater Than Greater Than or Equal To |
For Range or Between conditions, combine Greater Than or Equal To and the Less Than or Equal To.
Using AND/OR/Not
You can further refine your search conditions using the AND/OR/Not radio buttons.
For example, select the Not operation to retrieve data not equal to your search condition.
Quite precise search criteria can be specified by grouping two or more of these conditions together. Refer to 3.7.3 The Conditions Area for an example and details about grouping your conditions.
Value
The value to use with your search condition.
Data will be selected if it matches the value entered. If you are not sure of the actual length or composition of the value you can enter, a description is provided beneath the Value box for guidance. Your value cannot be longer than the field you have selected.
If you use the Is In Operator, each value you enter must be separated by a comma. Your Value can be a maximum of 1023 characters. To enter Values longer than that, you can use OR to link multiple Values.
You can insert All values here, if the value is to be entered via a Prompt dialog. This means that your query will pause and wait for a value to be entered in the Prompt Condition dialog box.
If you want to select all values and you are not using a Prompt, then you should use the Like operator instead of the generic "All values".
When you use the Like operator, you can use these wildcard characters in your value:
- % or * to indicate more than one character.
- ? or _ to indicate a single character.
For example, if your Value is:
|
Wils% or Wils* |
you will retrieve Wilson, Wilshire, Wilshaw, Wilsmore, etc. |
|
Wils % or Wils * |
you will retrieve Wils John, Wils Duncan. |
|
Wil_s% or Wil?s* |
you will retrieve Wills, Wilesley, Wilms, etc. |
|
%Wil% or *Wil* |
you will retrieve names which have Wil somewhere in them, such as David Willis, Jo O'Wilket, etc. |
LANSA Client will append the % character to the end of the value entered if the field is longer than the value you have entered and if you haven't used a wildcard character.
If the value can vary each time the query is run, for example you want to select sales by date, enter a default value here and specify in the Prompting area that you want to enter a Value when the query is run.
Make condition case sensitive
Ideally, it is better to select alphabetic data which exactly matches the case of the Value you have entered.
For example, if your Value is to select product code ABC% or ABC*, your query will run faster if the Product Codes in the database are all in upper case, that is ABC123.
Deselect this option if you do not want the selection to match the case of the value entered. In this case, for Product Code ABC% or ABC*, you could retrieve product codes ABC123, abc123, Abc123 and so on. By deselecting case matching, your query will take longer to execute. As a rule, you should only turn this feature off if you really have to.
Note: Some databases are case-insensitive, and upper and lower case characters are treated as equivalent. For these databases, the case of the Value entered is ignored when retrieving data. For example, if you entered ABC as the Value, with an Operator of 'is like' and with the option selected, you could receive back AbCd, ABCD, abcd and so on. This is normal behaviour for case-insensitive selection. Your database administrator will be able to advise you if your host database is of this type.
Date and Time Entries
To the right of the Value, is a button labelled "...".
Clicking this will open the DateTime Selection dialog, where you can select a date from a Calendar and enter Hour, Minute and Second values.
For more information, refer to DateTime Selection.
Further Information