Conversion of Numeric Search Keys to Strings in Queries

Some data sources, such as Teradata, do not make the conversion, which causes an error.

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.

You can use the RSVP.PROMPT.CASTNUMERICSEARCHKEYTOSTRING setting to convert numeric data items into a string (varchar) format. The default value for the setting is False (no conversion). To enable conversion, set it to True.

The associated error message occurs 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

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'