Archives of the TeradataForum

Message Posted: Tue, 15 Sep 2009 @ 12:56:30 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Folks,

I am not able to understand some INTEGER to VARCHAR data conversion behaviour of Teradata 12.

Could you please help me understand.

Here is my experiment and its result.

I created a table with an Integer column

     create table my_tab
     (cl1 integer);

Then I inserted three values into the table:

     insert into  my_tab values(1);
     insert into  my_tab values(14444);
     insert into  my_tab values(144);

When I run select query on this table I get three rows as expected.

Now I create another table with a varchar column:

     create table my_tab_ch
     (cl1 varchar(10));

Now I insert values in this table from previous table as follows:

Experiment 1

     insert into my_tabch sel * from my_tab;

Strangely when I sel from this table my_tabch. I get three rows as expected but the one row with value 1 is missing instead of 1 value is blank. This is not null. Why so?

Experiment 2

Then I insert a value in varchar column as follows (note the integer value):

     insert into my_tabch values(13333);

Experiment 2

And now I insert a character value as follows (note the quotes):

     insert into my_tabch values('13333');

When I run the length function on this column, I again see the strange result. The length of the value inserted as integer comes out to be 6 whereas value inserted as string is 5 in length. All other values inserted via Insert..select statement have 10 length, the size of column.

My questions are:

1. if Integer value is having only 5 digits then should it not be a 5 char string in VARCHAR column? If not what govern this behaviour?

2. Why INTEGER value 1 is not inserted in VARCHAR column and instead a space of 0 length was inserted.

3. why same value 13333 inserted as INTEGER and string inserted differently. Again what govern this behaviour?

thanks in advance...

Prahlad

	<Prev	Next>		<<First	<Prev	Next>	Last>>