TD 14 - Data Storage Questions

Database
Enthusiast

TD 14 - Data Storage Questions

--create table 
CREATE SET TABLE database.table,
 NO FALLBACK ,
 NO BEFORE JOURNAL,
 NO AFTER JOURNAL,
 CHECKSUM = DEFAULT,
 DEFAULT MERGEBLOCKRATIO
 (
        some columns...,
 unique_number_10_digits CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
 )
PRIMARY INDEX( unique_number_10_digits );

--inserts
INSERT INTO database.table VALUES(some values..., 1234567890);

Hi all,

I have used this forum a lot so thank you in advance for the great information that has been really helpful to me and others so far.  But I have a question that I can't seem to find an answer to.  Basically I wanted to insert a 10 digit number (e.g. 1234567890) into a CHAR(10) column but only 9 digits were inserted.  The 10 digit number was not encased in single quotes in my insert statement.  I assume this mean there is some kind of implicit conversion behind the scenes (CAST maybe?).  I also tried creating the column as a VARCHAR(20) to see what would happen and something strange also occured.  When I inserted the numbers there was 10 digits, but some of the numbers had a fullstop at the end (e.g. 1234567890.).  I pasted my code into Microsoft Wordpad to look for Unicode characters but didn't find any.  So I have a couple of questions.

1) When trying to insert 10 digits that are not encased in single quotes into a CHAR(10) column, why does only 9 get inserted?

2) When trying to insert any number of digits as a string, should I encase them in single quotes? (i.e. '1234567890' instead of 1234567890).

3) When trying to insert any number of digits as a string, if I don't encase them in single quotes is there an implicit conversion going on behind the scenes? If so, what type of implicit conversion?

4) When trying to insert 10 digits that are not encased in single quotes into a VARCHAR(10) column, why would some have fullstops at the end and others not?

N.B. I have pasted some code into here so you can see what I was using when this situation happened.

4 REPLIES
Teradata Employee

Re: TD 14 - Data Storage Questions

1) See answer # 3

2) That's the preferred option - if the data is supposed to be a character string, use a string constant (even if the characters happen to be digits).

3) If you SELECT 1234567890 as x, TYPE(x), FORMAT(x);

You will see that the value is being interpreted as an INTEGER, and that the default FORMAT is 11 characters long (to allow for the optional sign). The implicit conversion to a character string yields ' 1234567890' (note the leading space). An explicit ANSI-style CAST would TRIM the spaces, but implicit or Teradata-specific data type conversion does not.  

4) If you do a similar test with a 9-digit value that doesn't fit into an INTEGER,  such as 9876543210, you will see that those values are treated as DECIMAL(10,0) and the FORMAT includes the trailing '.' - though if the target was VARCHAR(10) that character would be truncated.

Enthusiast

Re: TD 14 - Data Storage Questions

Thanks a lot for the quick and detailed response, this makes sense now!

Enthusiast

Re: TD 14 - Data Storage Questions

Hi sallad,

If you are new to Teradata, I would suggest to read Teradata SQL Bascis guide. It will help you to answer all these and many more questions.

Khurram
Enthusiast

Re: TD 14 - Data Storage Questions

Thanks Khurram, I have done the introduction to Teradata 1 day training course and the 4 day basic SQL course, but it was a while ago and I hadn't used SQL / Teradata for a few months so I forgot a lot.  Thanks though, I will check out the Teradata SQL Basics guide.