Integer formats

Database
Enthusiast

Integer formats

Hi
I have some doubts over integer formats...

I created 3 tables and ran similar set of queries
1)create table table1(a integer format '-(10)9')
insert into table1 values(123456)
sel a (char(6)) from table1;
output=1

2)create table table2(a integer format '-9(10)')
insert into table2 values(123456)
sel a (char(6)) from table2;
output=00001

3)create table table3(a integer format '(10)9')
insert into table3 values(123456)
sel a (char(6)) from table3;
output=000012

I am not able to understand why different outputs are coming
and more precisely how signed/unsigned is handled here...

also what is the differenece between '-9(10)' & '-(10)9'
3 REPLIES
Teradata Employee

Re: Integer formats

Format '-(10)9' is a "floating minus" while '-9(10)' is fixed location for the sign and leading zeros shown. If you were to CAST to CHAR(11), 123456 would be BBBBB123456 (where B is a space) with '-(10)9' but B0000123456 with '-9(10)'.
For comparison, -6789 would be BBBBBB-6789 or -0000006789 respectively.

Since you are casting to a field that is too small, the string is truncated after formatting. Note also that an ANSI-style CAST(a as CHAR(6)) will trim leading blanks (left-justify) while Teradata-style (CHAR(6)) will not.
Enthusiast

Re: Integer formats

Thankx a lot Fred....
Could you please suggest some book where we can find such kind of elaborate explanations on data types and literals....as the book usually suggested--"Data Types & Literals" is not having all these...It would be a great help....
Teradata Employee

Re: Integer formats

The "Data Types & Literals" manual does describe the meaning of the FORMAT phrase itself, but details regarding data type conversions are in the "Functions & Operators" manual.