NCR RDBMS ERROR 2620 1

[DFW1999]

Hello,

While running a query without using cast or format with multiple joins to Five(5) tables against V2R5 Teradata I am getting "2620 Error" which has the following description from docs:


2620 The format or data contains a bad
character.
Explanation: This error indicates that the user
submitted a numeric-to-character conversion with
illegal format, or that, in a character-to-numeric conversion,
the data or the format contains a bad character.
Generated By: The interpretative instruction processor.
For Whom: End User.
Remedy: Verify the format used. If it is legal, then
illegal character is present in the data being converted
from character to numeric.


As I am not converting or casting any data type in my query the other reason can be the existence of bad character or data.

How come I can confirm that if there is bad data in the tables.( tables have records in millions)

Any solution?

thanks

 

[rrrkrishnan]

There is a character in the column which is declared as a char datatype that you are joining with a column which is of type numeric. It could be spaces , try to trim spaces and then join.
If that doesn't work, try doing a cast to integer or decimal of the column (involved in the join) declared as character data type and do just a select cast(col1 as integer) from that one table. Do this for each of the five tables in the join. If the 'SELECT' fails returning this error 2620, you can nail down the col and the table which is causing this issue.
Hope this helps.

 

[WILLIEWANKA]

This could also happen if you are performing an insert into a numeric field and you are concatenating large numeric fields without formatting them. Especially integer fields, when concatentating them, they are cast as characters with leading spaces. So 1 would actually be " 1".




CREATE TABLE TEST (
NUMBER_1 INTEGER,
NUMBER_2 INTEGER,
NUMBER_3 INTEGER)

INSERT INTO TEST
(NUMBER_1,NUMBER_2,NUMBER_3) SELECT 1,2,3

INSERT INTO TEST
(NUMBER_1)
SELECT
NUMBER_2||NUMBER_3
FROM
TEST

**Code = 2621.
2621: Bad character in format or data of U61199_TEST_INTEGER.NUMBER_3


SELECT
NUMBER_2||NUMBER_3
FROM
TEST



Results:
(NUMBER_2||NUMBER_3)
2 3

To remedy this, just format the column


SELECT
(NUMBER_2 (FORMAT '99')) || (NUMBER_3 (FORMAT '99'))
FROM
TEST


Results:
(NUMBER_2||NUMBER_3)
0203