Testing your Select statement in the Query form

In the Query form, you can click Run Query to test your SQL code before you save it to the database.

This button is displayed only for queries that begin with a SELECT statement. If you remove the SELECT statement from your query, the button disappears when you tab out of the Query Text field.

The Run Query button can be used to test queries without tokens or queries that contain these tokens:

  • [%%OBJECTSYMBOL], [%OBJECTSYMBOL]
  • [%LAB]
  • [%%ROLES], [%ROLES], [%%GROUPS], [%GROUP], [%USER]
  • [%LANGUAGE]
  • [%%LIKECLAUSE]
  • [%OBJECTKEY], [%REFOBJECTSYMBOL], [%REFOBJECTKEY]

Valid tokens not supported by Run Query

Certain tokens, although valid, are not currently supported by the Run Query function. Therefore, these tokens are not displayed in the tokens table. If you try to run a query with any of these tokens, an error is displayed. Here is the list of these tokens:

  • [%CRITERIA]
  • [%%CURRENT_DB_NAME]
  • [%%CURRENT_SESSION_ID]
  • [%%PIVOT_PARAM]
  • [%%SEARCHTABLE]
  • [%%CUSTOM]
  • [%%TABLE]

There is one special case, [%%DBO]. Queries that use this token can be tested using Run Query; however, substitutions are not permitted. So the default value for [%%DBO] is always the user’s database.

Running the query test

In the Query form, complete these steps:

  1. Specify your SELECT statement in the Query Text field.
  2. Tab out of the field.
  3. Optionally, specify the values for the token prompts. Where appropriate, the default values are displayed for you in the Value column. For example, the current user is displayed in the Value column for the [%USER] token. You can override this default value.
    • For most token values, exclude quotation marks from the value. For example, specify 100 for [%1], not '100'.
    • Two tokens, [%%GROUPS] and [%%ROLES], require you to specify single quotes around the individual values. For example, 'TECHNICAL','ADMIN'.
    • Only tokens that you can test with the Run Query button are displayed in the table.
  4. Click Run Query.

A maximum number of 1000 rows are displayed in the Query Results tab. This row limit is imposed to preclude issues with system performance. You can see the actual values that you specified for the token prompts in the Resolved SQL field.

Color-coding in the Query Text field

This table shows the colors that are used in the Query Text field:

Color Description
Blue SQL keywords
Purple Supported tokens
Violet Read-only tokens
Red Unsupported tokens
Green Quoted strings

Example of testing one query

This example shows you how to test your query for one object, Item - 01001.

queryformtestex1

In the Resolved SQL field, the [%%LIKECLAUSE] token is replaced with the value, 01001. The item, 01001, is displayed in the Query Results grid.

querytestex2