All Forums Database
prakhar 101 posts Joined 05/08
27 Feb 2009
Integer formats

HiI have some doubts over integer formats...I created 3 tables and ran similar set of queries1)create table table1(a integer format '-(10)9')insert into table1 values(123456)sel a (char(6)) from table1;output=12)create table table2(a integer format '-9(10)')insert into table2 values(123456)sel a (char(6)) from table2;output=000013)create table table3(a integer format '(10)9')insert into table3 values(123456)sel a (char(6)) from table3;output=000012I am not able to understand why different outputs are comingand more precisely how signed/unsigned is handled here...also what is the differenece between '-9(10)' & '-(10)9'

Fred 1096 posts Joined 08/04
27 Feb 2009

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.

prakhar 101 posts Joined 05/08
28 Feb 2009

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....

Fred 1096 posts Joined 08/04
28 Feb 2009

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.

You must sign in to leave a comment.