gilta03

Tech Tip: Subset Error- The format or data contains a bad character

Discussion created by gilta03 Champion on Nov 30, 2017

This document can also be viewed at https://support.ca.com/us/knowledge-base-articles.TEC1483628.html 

To see more documents like this, check out our TDM knowledge base here: CA Test Data Manager 

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

Outcomes