Issue with Decimal(38,0) data type in BTEQ

Database
Enthusiast

Issue with Decimal(38,0) data type in BTEQ

Hi,

I am trying to export data from a table in which one column  has a data type of Decimal(38,0)

Query: select sa from aa;

Result:

When I run the select statement in SQL assistant....the output is fine....like below

sa

1 1,234,567,890,123,456,789,012,345,678

When the same query is run through BTEQ there is a decimal point coming at the end of the value..like below:

                                      sa

----------------------------------------

           1234567890123456789012345678.

I am trying to export this data in batch mode...I do not want to have this decimal point at the end ......

Please help me out if there is any format string for the this....I have tried format '9999999....99'...but this is allowing till 30digits...I found that it format supports only 30digits...but ther should be some alternate to avoid this dot(.).....your quick reply is really appreciated on this...

Thanks...

Tags (3)
7 REPLIES
Senior Apprentice

Re: Issue with Decimal(38,0) data type in BTEQ

Try FORMAT 'Z(38)' or FORMAT '9(38)'

Dieter

Enthusiast

Re: Issue with Decimal(38,0) data type in BTEQ

Thanks Dieter.....Both of them are working.....

Re: Issue with Decimal(38,0) data type in BTEQ

i too found the same problem. I tried FORMAT 'Z(38)' or FORMAT '9(38)'. It worked. Thanks for Sharing!!

Re: Issue with Decimal(38,0) data type in BTEQ

I have tried this code, but this is converting negative decimal to positive. Can you help for not to convert this to positive decimal.
Senior Apprentice

Re: Issue with Decimal(38,0) data type in BTEQ

For negative values you need to include the sign: '-(38)9'

Dieter

Enthusiast

Re: Issue with Decimal(38,0) data type in BTEQ

Hi,

I have created a table like create table a(col1 decimal(38,0),b integer)

i want to keep my col1 as identity. But when i look at the table in bteq it shows a dot after the value.

Why does this dot appear. is there any setting required???

Can i create this col1 as identity or i should use BIGINT. Which is preferable.

Thanks,

Senior Apprentice

Re: Issue with Decimal(38,0) data type in BTEQ

You can use any DEC(x,0) as identity, but the range of a BIGINT is probably enough and it's only 8 bytes instead of 16 bytes for storage.

And the setting to remove the dot is an appropriate format.

Dieter