Subset Error: The format or data contains a bad character

Document ID : KB000008717
Last Modified Date : 14/02/2018
Show Technical Document Details
Issue:

While using the Preview Rowcounts feature in Subset with our Teradata connection, I am receiving the following error message: 

Database Error Message: 
[Teradata Database] [TeraJDBC 14.10.00.17] [Error 2620] [SQLState 
HY000] The format or data contains a bad character.

Environment:
CA Test Data Manager (TDM)- Subset, Teradata
Cause:

If some of the data in one of your columns is bad or the format is incorrect, then you will receive this error message. The cause and resolution is typically very customer specific. Here are some common reasons:

  • Using non numeric data
  • Data types used incorrectly
  • Query formed incorrectly
  • Not using a comma as a separator 
  • Comparing integer values with varchar
  • Joins are incorrect
  • Spaces in the data
  • Alphanumeric characters where numeric is required
Resolution:

We highly recommend working with your database administrator as they can easily help find the root cause of the issue with you.

 

The most common cause we have seen is the use of non numeric data. You can use the following SQL query to test for this:

SELECT 
tdata – THEIR CHARACTER JOIN COLUMN 
,CASE WHEN (POSITION(' ' IN TRIM(tdata)) > 0) OR (UPPER(TRIM(tdata)) (CASESPECIFIC) <> LOWER(TRIM(tdata)) (CASESPECIFIC)) 
THEN 'Non-Numeric' 
ELSE 'Numeric' End 
FROM ORDERS.test2 
ORDER BY 2,1; 

Once you remove any bad data or fix the format issue, this issue will be resolved. Again, the resolution will be very specific to your data and environment, so it is best to work with your company's DBA to resolve this. 

Additional Information:

If you experience any further issues, please open a support case by going to https://support.ca.com/us.html