Subset Error: The format or data contains a bad character

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

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.broadcom.com.