BTEQ behaviour for Decimal(10,0) and Deciamal(18,2)

Tools & Utilities

BTEQ behaviour for Decimal(10,0) and Deciamal(18,2)

Hi All, I am tryign to export data from teradata to unix file and facing issue with Decimal.

Table structure involved is 

CUSTOMER_IDENTIFIER DECIMAL(10,0) NOT NULL

CUSTOMER_BALANCEE DECIMAL(18,2) NOT NULL

Values :

CUSTOMER_IDENTIFIER = -229286382

CUSTOMER_BALANCE = -0.08

Issue 1 and solution i found :

First i was using TRIM(CAST(CUSTOMER_IDENTIFIER AS CHAR(100))) , however i was getting -229286382. as output [ dot (.) at the end ]

To eliminate this i modified above statement to  TRIM ( CAST(CUSTOMER_IDENTIFIER AS FORMAT'Z(20)9')) which gives 229286382 (No dot (.) ) but eliminate -ve(-) sign. Hence i again modifed to TRIM ( CAST(CUSTOMER_IDENTIFIER AS FORMAT'-(20)9')) which worked perfectly.

Issue 2 , no solution :

CUSTOMER_BALANCE is not working with any of the above formatting. values i am getting as below:

TRIM ( CAST(CUSTOMER_BALANCE AS FORMAT'Z(20)9')) = 0

TRIM(CAST(CUSTOMER_BALANCE AS CHAR(100))) = -.08  [ Notice 0 is missing ]

TRIM ( CAST(CUSTOMER_BALANCEAS FORMAT'-(20)9')) =0 

Can we have any single format which would handle both issue?

2 REPLIES
Teradata Employee

Re: BTEQ behaviour for Decimal(10,0) and Deciamal(18,2)

There is no single FORMAT that handles both cases as you would like.

 For CUSTOMER_IDENTIFIER, FORMAT '-(10)9' would suffice. Note that this allows for 11 total characters, the "floating" minus sign followed by 9 optional digits and one required digit (total of 10 digits). 

For CUSTOMER_BALANCE, you seem to want FORMAT '-(16)9.99' which would be "floating" minus sign followed by up to 15 optional digits, 1 required digit before the decimal, and two digits after the decimal (total of 18 digits).

Re: BTEQ behaviour for Decimal(10,0) and Deciamal(18,2)

Thanks Fred. It works perfectly.