Using Query at Runtime in Advanced SQL

You can optionally include Query At Runtime parameters in your advanced SQL statement. The Query At Runtime definition is composed of the following:

%QAR{{ Table.Column, User text }}%

Note the Query At Runtime definition starts with the string %QAR{{ and ends with }}%. The internal definition consists of the following two parts:

Table.Column – Defines the database table and column to use. Note that the table name is optional; if it is omitted, the table selected in the Report Item Type field is assumed and the following format is used: %QAR{{Column, User text}}%.
User text – User-defined text that accompanies the field name on the Query At Runtime report page. You can provide text of your choice or a phrase that can be localized.

The following table lists User text phrases that are automatically replaced with localized text when shown to the user who runs the report:

User text	Localized text shown to the user
all	contains all
any in	contains any
not all	does not contain all
not any not in	does not contain any
like	like
not like	not like
likez	like (zero-filled)
contains	contains
not contains	not contains

In addition, note the following:

The Query At Runtime definition is used the same way in both and pass-through SQL and SBM-processed SQL.
Fields in the Basic Conditions section of the report definition always appear before any fields in the advanced SQL statement. Also, Query At Runtime fields in advanced SQL are displayed in the order they are defined in the advanced SQL query.
The following field types are not supported fields with advanced SQL Query At Runtime:
- System fields
- Multi-Relational, Multi-Selection, Multi-User, and Multi-Group fields

Example of using Query At Runtime with the Last Modified Date field:

@WHERE 
  UIM_INCIDENTS.TS_LASTMODIFIEDDATE > 
  %QAR{{ UIM_INCIDENTS.TS_LASTMODIFIEDDATE, is after }}%

Example of using current user with the Owner field in pass-through SQL:
```
@WHERE 
  TS_OWNER in (%{{Current User}}%)
```

Examples of using Query At Runtime with a Text field:

@WHERE 
  TS_ISSUEID like '%%QAR{{TS_ISSUEID, contains}}%%'

@WHERE
  TS_ISSUEID like '%%QAR{{TS_ISSUEID, contains}}%%' OR
  TS_ISSUEID like '%%QAR{{TS_ISSUEID, contains}}%%' OR
  TS_ISSUEID like '%%QAR{{TS_ISSUEID, contains}}%%'

Examples of using Query At Runtime with a Date/Time field:

@WHERE 
  TS_EST_DATE_TO_FIX >= %QAR{{UBG_ISSUES.EST_DATE_TO_FIX, >=}}%

@WHERE TS_EST_DATE_TO_FIX > %QAR{{UBG_ISSUES.TS_EST_DATE_TO_FIX, >}}% and
  TS_EST_DATE_TO_FIX < %QAR{{UBG_ISSUES.TS_EST_DATE_TO_FIX, <}}%

Example of using Query At Runtime with a Binary field:

@WHERE 
  TS_P4STATUS = %QAR{{UBG_ISSUES.P4STATUS, =}}%

Example of using Query At Runtime with a User field:

@WHERE 
  TS_DEVELOPER in (%QAR{{UBG_ISSUES.TS_DEVELOPER, in}}%) and
  TS_OWNER in (%QAR{{UBG_ISSUES.TS_OWNER, in}}%)

Example of using Query At Runtime with a Numeric floating point field:
```
@WHERE 
  TS_FLD_NUM_2_FP > %QAR{{UBG_ISSUES.TS_FLD_NUM_2_FP, >}}%
```

Example of using Query At Runtime with a Numeric integer field:

@WHERE 
  TS_FLD_NUM_1_INT > %QAR{{UBG_ISSUES.TS_FLD_NUM_1_INT, >}}%

Example of using Query At Runtime with a Single-Relational field:

@WHERE 
  TS_FLD_SINGLE_RELATIONAL in (%QAR{{TS_FLD_SINGLE_RELATIONAL, in}}%)

Using Query at Runtime in Advanced SQL

Related Topics