[TOC] [Prev] [Next]

Using the Query Bar

Use the query bar, as shown in the figure below, when you want to define a query that does more than simply find ARs with fields that match an example. The query bar lets you define more complex logical statements. For example, you may want to match one field or another. You also use the query bar whenever you want to use a Status History field item in your query. Remember that the criteria you enter in the query bar are used in conjunction with any criteria defined in the query-by-example section of the window.

Figure: Query Bar

You can define a query statement in the query bar by using any of the following techniques:

Note: You can hide the query bar from view when it is not used. To hide (or display) the query bar, display the query preferences. See Setting User Preferences.

You must follow certain formatting conventions in constructing the query statement. The sections that follow describe these conventions as well as the logical and arithmetic operators that you can use. You can also use the Edit menu operations (Cut, Copy, Paste, Clear, and Copy All) in the Query Bar.

This section contains information about the following topics:

Entering information in the query bar

  1. Add a field name to the query bar using any of the following methods:

    2. For example, if you select the field name Ticket Status, you see: 'Ticket Status'

    Type an operator or select the appropriate operator from the palette in the query bar.

    The operator is added to the query bar.

    For example, if you select (or type) the equals sign (=), you see: 'Ticket Status' =

  2. Select another field or type a field value.

    3. When typing in a field value, non-numeric values must be enclosed in double quotes. These values are case and space-sensitive.

    For example, you could enter Open in the query bar. For example: 'Ticket Status' = ''Open''

  3. Continue adding operators, field names, and field values until you have completed the query statement. You can put spaces between operators, field names, and field values, but you don't have to.

    4. For example: 'Ticket Status' = ''Open'' OR 'Ticket Status' = ''Escalated''

  4. When you are ready to perform the query, select the query operation that you want from the Query menu. The query operations are described in the section Query Operations later in this chapter.

[Top]

Query Bar Format and Syntax

You must follow a set of formatting conventions when you build a query in the query bar. You can use the relational operators and wild card symbols shown in Relational Operators Used in the Query Window and Wild Card Symbols in the query bar. You can also use the operators shown in Operators Used in the Query Bar.

The preceding example, 'Ticket Status'=''Open'', finds only the ARs with a status of "Open". By itself, this may be the lowest common denominator in your search but it may also provide you with far too many matches. To avoid the frustration of going through all of them, you can refine your search by using the query bar operators. Refining your query criteria helps you narrow down the number of ARs you need to deal with when searching for a specific answer.

[Top]

Using Operators in the Query Bar

You can use the operators shown in the table below in the query bar in addition to the wild card characters described earlier in Using Wild Card Symbols.

Table: Operators Used in the Query Bar
Operator Action

AND
&&

Logical AND of the result of two conditions (the result is true only if both conditions are true).

For example, 'Status'=''New'' AND 'Assigned-to'=''Andy'' would find all new ARs assigned to Andy. You can use the symbols && instead of the word AND.

OR
||

Logical OR of the result of two conditions (the result is true if either condition is true).

For example, 'Status'=''New'' OR 'Assigned-to'=''Andy'' would find all new ARs and all ARs assigned to Andy (no matter what their status). You can use the symbol || (two vertical lines) instead of the word OR.

NOT
!

Negates the condition that follows (if the condition is false, the result is true).

For example, NOT 'Status'=''New'' would find all ARs that are not new. You can use the symbol ! (exclamation point) instead of the word NOT.

LIKE

 Performs a pattern search.

For example, 'Submitter' LIKE ''Bob%ton'' would find all ARs with a submitter name that begins with the letters "Bob" and ends with the letters "ton" - such as Bob Compton and Bobby Fenton. The LIKE operator is useful only with character and diary type fields. Note, using square brackets and the LIKE operator works for flat files, Sybase, and Ingres. Using square brackets and the LIKE operator does not work with Oracle or Informix. Refer to the Action Request System Installation Guide for more information.

+

  • Adds two integer or real values.
  • Adds an integer interval to a time value.
  • Concatenates two character strings.

    • For example, 'Create-date' > $DATE$ + (28800) would find all ARs that were created after 8:00 am today. (28800 is the number of seconds in 8 hours.)

    -

  • Subtracts two integer or real values.
  • Subtracts two time values (resulting in an integer).
  • Subtracts an integer interval from a time value.

    • For example, 'Create-date' > $DATE$ - (604800) would find all ARs that were created within the past week. (604800 is the number of seconds in one week.) This is useful to include in a custom report that you run weekly that creates a report of all ARs created in the past week.

    *

    		Multiplies two integer or real values.

    For example, 'Quantity' * 'Price' > 50 finds all ARs where the contents of the Quantity field multiplied by the contents of the Price field is over 50.

    /

    		Divides two integer or real values.

    For example, 'Total Expenses' / 'Total Income' > 1 would find all ARs where the total amount spent for expenses equaled the total amount brought in as income.

    %

    		Modulo of two integer values (the remainder of a division of the values). Since a percent sign is also a valid wild card symbol, the context when using a percent sign determines how it is interpreted.

    For example, 'ID' % 2 = 1 finds all ARs with an odd number in the ID field.

    <

    		Matches contents that are less than the value.

    For example, 'Create-date' < ($DATE$ - 86400) would find all ARs created more than 24 hours ago. (86400 is the number of seconds in 24 hours.)

    >

    		Matches contents that are greater than the value.

    For example, 'Create-date' > "06/10/94 00:00:00" would find all ARs with Create-dates that are newer than at midnight June 10, 1994.

    !=

    		Matches contents that are not equal to the value.

    For example, 'Status' != "Closed" finds all ARs that aren't closed.

    <=

    		Matches contents that are less than or equal to the value.

    For example, 'Salary'<= 10000 would find all ARs where the contents of the Salary field is less than or equal to 10000.

    >=

    		Matches contents that are greater than or equal to the value.

    For example, 'Create-date' >= "10/31/94" would find all ARs with Create-dates equal to or more recent than October 31, 1994.

    =

    		Matches contents that are exactly equal to the value. For example, 'Status' = 0 would find all ARs with a status value equal to the first selection value.

    [Top]

    Operator precedence

    When you use multiple operators in constructing qualification criteria, they are executed in the following order of precedence (in the following order that they appear within the qualification):

    1. ( )

    2. !, NOT, - (unary minus)

    3. *, /, %

    4. +, -

    5. <, <=, >, >=, =, !=

    6. && (AND)

    7. || (OR) If the same operator is included more than once in the same qualification (or two operators of the same precedence are used in the same qualification), the first time each operator is found going from left to right it is per

    [Top]

    Query bar statement conventions

    Use the following conventions to construct a query statement in the query bar.

    Fields

    Note: If a field name contains a single quote, you must double the embedded single quote (that is, use two single quotes). For example, if the field is named "Submitter's Phone Number" you need to enter it as 'Submitter''s Phone Number'. This is done for you automatically if you select the field name from the Field List dialog box or by selecting the label in the query-by-example section.

    Values

    Status

    [Top]

    Examples of query statements

    'Submitter' != $USER$

  • Find all ARs that were submitted after 10:00 am on the current day. The example uses the greater than operator (>) to find ARs where the value of the Create-date field is greater than the current day at 10:00.

    • 'Create-date' > ''10:00:00''

  • Find all ARs that have been submitted for any problem having to do with printing. The example uses the LIKE operator to perform a pattern search that finds ARs with the word "Print" or "print" anywhere in the Submitted Problem Type field. The example works for flat files, Sybase, and Ingres. Using square brackets and the LIKE operator does not work with Oracle or Informix. Refer to Database Considerations in Chapter 4 of the Action Request System Installation Guide for more information.

    • 'Submitted Problem Type' LIKE ''%[Pp]rint%''

  • Find all ARs with a Status of Released. Notice the spaces after the word "Status" in the field specification. In this case, the spaces exist in the field label as defined on the schema being used. Because the AR System recognizes spaces that exist, the spaces must also be included in any qualification statement for that field. If you use the Field List dialog box by selecting the Fields button on the qualification bar, the spaces (and single quotation marks) are added for you automatically.

    • 'Status ' = ''Released''

    [Top] [TOC] [Prev] [Next]

    support@remedy.com

    Copyright © 1996, Remedy Corporation. All rights reserved.