Using Query by Example with data types

A value in a QBE expression must match the data type of the data source field to which it applies. For example, you must use a numeric value in a QBE expression that filters a numeric field. Do not include characters, such as commas used as placeholders, when typing a numeric parameter value.

Using a date in a QBE expression

To supply a date in the short date format, use the locale-specific date separator and date format order.

Table 4-8 lists examples of correct date formats in different locale settings.

Table 4-8  Using a date in a QBE expression 

Locale setting	Correct format
US English	01/15/2011
French (France)	15/01/2011
Russian	15.01.2011

When using a date in a QBE expression, use four-digit years rather than two-digit year abbreviations. For example, to signify January 15, 2011, type 01/15/2011 rather than 01/15/11.

Using a null value in a QBE expression

To specify that a field in the database must or must not contain a data value, use the keyword Null. To specify that the field must not contain a data value, type Null as the QBE expression. To specify that a field must contain a data value, type !Null as the QBE expression. Null is not case sensitive.

Matching an exact string value in a QBE expression

To ensure a match with only exact values from the data sources, enclose the text string in single quotation marks in the QBE expression. Returned data must match the enclosed string exactly. Returned data does not include values that have additional characters at the end of the string. A comma appears as a list separator for Smith, Jane. Use the pipe sign (|) to specify a list separator that is locale independent.

Table 4-9 lists the values that example QBE expressions return.

Table 4-9  Matching an exact string value in a QBE expression 

QBE expression	Matches	Does not match
Smith	'Smith' or 'Smith ' or 'Smithson'
'Smith'	'Smith'	'Smith ' 'Smithson'
Smith, Jane	'Smith, Jane' or 'Smith, John' or 'Janesson, Per'
'Smith, Jane'	'Smith, Jane'	'Smith, John'
Red|Green|Blue	'Green' 'Red|Green|Blue'
'Red|Green|Blue'	'Red|Green|Blue'	'Red' 'Green' 'Blue'

Making a literal character in a QBE expression

A specific syntax is required when a QBE expression attempts to match a value using an operator character as a literal character. Table 4-6 shows available operator characters.

Type a backslash (\) before each special character, or enclose the string in single quotation marks, which directs the application to match the string value exactly. For example, typing a backslash before the comma directs the application to interpret the comma literally in the following QBE expression:

16M x 1 Dynamic Ram\, 3.3 volts

Without the backslash, depending on your locale setting, Information Console interprets the comma as an OR.

Use the pipe sign (|) to specify a list separator that is locale independent.

The following QBE expression matches the percent sign (%) literally in a string:

'ab\%c'

Matching character sets and spaces with the percent sign

The percent sign (%) matches any set of characters or blank characters. Information Console adds a percent sign to a string supplied as a QBE expression when all the following conditions are true:

n	The database column is of type string.

n	The input parameter value is not enclosed in single quotation marks and is not a range.

n	The string does not contain a percent sign, such as Sm%th.

Information Console adds a percent sign to ensure blank characters at the end of strings in the database do not interfere with the matching process. To stop Information Console from adding the percent sign (%) to the end of a string, enclose the string in single quotation marks in the QBE expression.

Table 4-10 illustrates the results of the application adding a percent sign to QBE expressions.

Table 4-10  Adding a percent sign to QBE expressions 

QBE expression	SQL condition	Matches	Does not match
'Smith'	custName LIKE 'Smith'	'Smith'	'Smith '
Smith	custName LIKE 'Smith%'	'Smith' 'Smith ' 'Smithe' 'Smithsonian'

To match values ending in a space character when the QBE expression contains a percent character, append a percent character to the QBE expression. For example:

Sm%th%

The above syntax is valid for a QBE expression with only a single value. Information Console does not add a percent sign to an expression for a range of values. For example, if the database column custName is a string and the report user types D as the value of the ad hoc parameter, the query retrieves the data row that contains Design Boards.

If the user types a range of values, such as A-D, for the ad hoc parameter value, Information Console does not match customer records where custName is Design Boards. This is because the value is a range and not a single value. For example, QBE expression values that retrieve values starting with A through D appear in Table 4-11, including one to which Information Console adds a percent sign.

Table 4-11  Examples of QBE expressions to retrieve a range of values 

QBE expression	SQL condition
A-E	custName BETWEEN 'A' AND 'E'
>A&<E	custName > 'A' AND custName < 'E'
A|B|C|D	custName LIKE 'A%' OR custName LIKE 'B%' OR custName LIKE 'C%' OR custName LIKE 'D%'

Matching character sets with brackets

Brackets ([ and ]) specify a set of matching characters. Information Console manages any special characters enclosed by brackets as literal characters. For example, the following QBE expression encloses the percent sign (%) and uses it literally:

ab[%]c

(c) Copyright Actuate Corporation 2011