Specify query conditions

You can specify query conditions for each entity or link type in the query. The condition applies to a selected item type, and if there is more than one item type in the query there can be multiple conditions.

Each line specifies a condition, which is in the form of field name, operator and value.

The available operators vary according to the field type. For more information, see List of operators.

Note:

  • Field values that are used in conditions are not case-sensitive, so entering 'findme' finds 'Findme'.

  • Dates are entered in the format that is determined by your Windows regional settings.

  • The @ character is a special character that is used to identify parameterized conditions. To find values that start with an @, you must prefix the entire value with an extra @. For example, if the value to match is @123 then you must enter @@123.

  • For coordinate queries, the conditions are automatically created when you enter the coordinate data in the Coordinate Query Builder.

Add a condition to a query

  1. In the Structure area, click the entity or link type for which you want to specify conditions.

    Note: You can only specify conditions on the 'Any Entity Type' item if you have defined standard fields or if the database is an SQL Server database and semantic types have been assigned.

  2. In Conditions, click the first line of the table select a field name from the list.

  3. Select an operator from the list.

  4. Move to Value 1, and then either select a value from the list or enter a value using the keyboard. If required, you can use wildcards or parameters.

  5. If you chose the between or outside operator, you will also need to enter a Value 2 to specify the other end of the range.

  6. You have now defined a condition for the entity or link type selected. Click Results to see the records found by this query.

  7. If required, you can now add a second condition for the same entity or link:

    1. To start a new condition, click a blank row.

    2. Define the condition.

    3. In the first column of the condition (the column without a heading), specify how this condition is combined with the other conditions. Select:

      • AND - if the record must meet both search criteria, as defined on this condition and the first condition.

      • OR - if the record must meet either the criterion that is defined on this condition or the criterion that is defined in the first condition.

      Note: AND operators are evaluated before OR operators.

Using wildcards in conditions

You can use wildcards in field values, but only when using these operators:

  • is like, isn't like

  • contains, doesn't contain

  • starts with, doesn't start with

  • ends with, doesn't end with

For other operators, the characters are interpreted literally. For example, when using the 'equal to' operator f*ndme finds f*ndme, not findme.

Note: Using the 'ends with' operator and searching for text, is the same as using the 'is like' operator to search for *text.

Using parameters for usernames, dates and times

You can use the following parameters in query conditions:

Parameter

Represents...

@#USER

The logged-on username.

@#NOWDATE

The current date.

@#NOWDATE +N/-N

The current date. You can include +N which represents a date 'N' days in the future, or -N which represents a date 'N' days in the past.

@#NOWTIME

The current time.

@#NOWTIME +N/-N

The current time plus or minus a specific number of hours. You can include +N which represents a time 'N' hours in the future, or -N which represents a time 'N' hours in the past.

Editing, inserting, repeating, and deleting conditions

At any time you can click a box in the Conditions area to change its contents.

You can also move the rows in the Conditions area - this may have an effect on the results of the query as the operators will be evaluated in a different order.

You can also insert and delete rows:

  1. Click the box at the left end of the row. An asterisk (*) appears in the box to indicate the current row.

  2. You can then:

    • Click Repeat to copy the current row and paste it above the current row.

    • Click Insert to add an empty row above the current one.

    • Click Delete to delete the current row.

Specifying how multiple conditions are combined

To specify clearly how multiple conditions are combined, you must add brackets. Anything within brackets is interpreted as one part of the query, and evaluated before anything outside the brackets. AND operators are evaluated before OR operators.

Examples:

  • (Sex is male and age is greater than 20) or eye color is blue - Finds all males over the age of 20 or anyone with blue eyes.

  • Sex is male and (age is greater than 20 or eye color is blue) - Finds all males who are either over 20 or have blue eyes.

  • Sex is male and age is greater than 20 or eye color is blue - This is the same as the first example because iBase evaluates AND operators first.

To add and remove brackets:

  • To add a bracket, double-click the '(' or ')', on the row where you want the bracket to appear.

  • Double-click a bracket to remove it.

Checking the position of brackets

To check the position of your brackets, you can select a bracket and press the F3 key. The block of text within the bracket and the other half of its pair is highlighted.

To remove the highlight, click anywhere in the grid.

Prompting for a field value when the query is run

You can parametrize the condition, for example, to prompt for a value when you run the query or to search for the current date.

Using semantic types in a query (SQL Server databases only)

You can use property (field) semantic types to search fields that are used in several entity types or link types and have different names, but store the same type of information. This feature is only available for SQL Server databases with semantic types assigned.

To use property semantic types:

  1. In Field, select Property Semantic Type

  2. Select the required property type.

  3. Continue to construct the condition and then run the query.

  4. You can review which particular fields have been searched after the query runs.