Implicit conversion CHAR to INT failing

Database
Teradata Employee

Implicit conversion CHAR to INT failing

Hi,

Have a following scenario:

Source table named TEST2:

CREATE SET TABLE Test2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Col1 INTEGER
)
PRIMARY INDEX ( Col1 );

Target table named TEST1:

CREATE SET TABLE Test1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Col1 CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC
)
PRIMARY INDEX ( Col1 );

Data contained in TEST2:
1
2
3
4
5

Command executed:
INSERT INTO TEST1
(
Col1
)
SELECT
Col1
FROM
Test2
;

Expected result:
Insertion of 5 rows mentioned above using implicit cast.

Actual result:
1 row is inserted with a empty string.

Can anyone explain what is the reason for this .... why implicit cast is not being used correctly in this scenario?

Thanks & Regards, MAC
2 REPLIES
Senior Apprentice

Re: Implicit conversion CHAR to INT failing

In fact it's used correctly :-)

You get an automatic typecast (= Teradata style typecast), which uses the format of the column, which is right aligned within 11 characters, i.e. leading blanks.
Then you cast it to 8 chars truncating the last 3 chars, which results in 8 blanks.
The target table is SET, which removes the duplicate rows,

SELECT FORMAT(test2.col1);
SELECT Col1 (CHAR(11)), Col1 (CHAR(8)) FROM Test2;

To fix it, you might use any of those:
- change the datatype of table1.col1 from CHAR(8) to CHAR(11)
- change the format of test2.col1: FORMAT '-(7)9'
- use TRIM(col1)
- use an ANSI style typecast, which cast left aligned with trailing blanks: CAST(col1 as CHAR(8))

Dieter
Teradata Employee

Re: Implicit conversion CHAR to INT failing

Thanks Dieter! Needed exactly that kind of detail over why its happening. :)

Regards, MAC