Conversion of numeric search keys to strings in queries

An error may occur if your data source does not convert numeric data items to strings.

A search prompt is associated with a query that does not get executed when the search prompt is rendered the first time. Typing a search string filters the query and the results are displayed in a list box. The report server does not check the data type of the filtered query item because most data sources convert the data item to a string (varchar) and the filter becomes valid. However, some data sources, such as Teradata, do not make the conversion, which causes an error.

The following error message is displayed when a Report Studio or Query Studio report runs:

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-69' UDA-SQL-0043 The underlying database detected an error during processing the SQL request.[NCR][ODBC Teradata Driver][Teradata Database] Partial string matching requires character operands.

Use the RSVP.PROMPT.CASTNUMERICSEARCHKEYTOSTRING advanced setting to convert numeric data items into a string (varchar) format. The default value for this setting is False (no conversion). To enable conversion, set this setting to True for the ReportService and the BatchReportService. For more information about configuring these settings, see Configuring advanced settings for specific services.

Example of unconverted data item

[data item] starts with '20'

[data item] contains '123'

Or a boolean combination:

[data item] starts with '2' AND [data item] contains '009' OR [data item] contains '119'

Example of unconverted data item with lower function

If the search is case insensitive then these expressions will contain the lower function, which makes more sense when searching on string data items than on numeric:

lower([data item]) starts with lower('20')

lower([data item]) contains ('123')lower

([data item]) starts with lower('2') AND lower([data item]) contains lower('009') OR lower([data item]) contains lower('119')

Example of data item converted to a string

cast([data item], varchar(128)) starts with '20'

cast([data item], varchar(128)) contains '123'

cast([data item], varchar(128)) starts with '2' AND cast([data item], varchar(128)) contains '009' OR cast([data item], varchar(128)) contains '119'