How to resolve "Data conversion exception" when using CAST function in TIBCO Data Virtualization?
Article ID: KB0073835
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | 8.3 HF-004 and higher |
Description
When using the CAST function with blank values: CAST('' as INTEGER), TDV throws an error "Data conversion exception - invalid character value to cast '' as 'INTEGER' " after applying TDV 8.3 HF-004.
Here is a screenshot:
Here is a screenshot:
Issue/Introduction
How to resolve "Data conversion exception" when using CAST function in TIBCO Data Virtualization?
Environment
All
Resolution
In TDV version 8.3 HF-004, there has been a change in default value for " SELECT { option " FORCE_ESTIMATION" } " parameter, which has been changed from -1 to 2. This update allows the query optimizer to perform cardinality estimation on query operators and query columns when statistics on the underlying query tables are available. However, this change may throw the exception when using CAST function as explained above.
Note: By default, in TDV v7.x and 8.3 the Postgres Database also throws the same exception whereas MS SQL Server does not.
To bypass this exception, change the CAST('' as INTEGER) to CAST(NULL as INTEGER).
Note: By default, in TDV v7.x and 8.3 the Postgres Database also throws the same exception whereas MS SQL Server does not.
To bypass this exception, change the CAST('' as INTEGER) to CAST(NULL as INTEGER).
Feedback
Yes
No
Powered by
