Formatting decimal fields while exporting

UDA

Formatting decimal fields while exporting

Hi,

I have a field in my table of data type decimal(7,4). While exporting to a file I want to format it in such a way that the output is :

input output
------ ----------
0.0000 .0
0.0320 .032
0.6654 .6654
0.1000 .1
99.5670 99.567

I have tried removing the extra zeroes by using trim(trim(both '0' from fld)). But this results in getting the value 0.0000 as '.'. I have also tried converting the decimal part as integer and appending with '.'.
This gives me the result but I want to know if any formatting can do it in an easy way.

Any sort of help on this is appreciated!

Thanks in Advance,
Chaitali
11 REPLIES
Teradata Employee

Re: Formatting decimal fields while exporting

Hello,

You can use anyone of the following:

- TRIM(LEADING '0' FROM fld) or
- TRIM(BOTH '0.' FROM fld) or
- Cast it to VARCHAR and then export, or
- Use FORMAT as follows:
SELECT CAST('0.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0010
SELECT CAST('99.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 99.0010
SELECT CAST('0.0000' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0000

HTH!

Regards,

Adeel

Re: Formatting decimal fields while exporting

Thanks Adeel!

I have tried out the options other than formatting. But none of them helps me get my required output.
Also as per your formatting options :

SELECT CAST('0.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0010
SELECT CAST('99.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 99.0010
SELECT CAST('0.0000' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0000

My output format will be different. I need to get .0 if my input is 0.0000 or 0.02 if my input is 0.0200.

Please let me know if you are aware of any such formatting.

Thanks,
Chaitali
Teradata Employee

Re: Formatting decimal fields while exporting

By the way .... why exactly do you need "0.123" to be ".123" .... ?

What you can do is, in your SELECT cast the formated value to VARCHAR and TRIM the LEADING "0"s. That should work.

Regards,

Adeel
Senior Apprentice

Re: Formatting decimal fields while exporting

Hi Chaitali,
did you work with Oracle before? :-)

AFAIK there's no format for suppressing trailing zeros.

TRIM(BOTH '0' FROM col (FORMAT '9(3).9(4)')) || CASE WHEN col MOD 1 = 0 THEN '0' ELSE '' END

Dieter

Re: Formatting decimal fields while exporting

Thanks Dieter!

Your solution works well! This is exactly what my output needs to be. But I was wondering if there is some formatting options which can do this as well.

Thanks a lot!
Chaitali
Teradata Employee

Re: Formatting decimal fields while exporting

Nice Dieter! :)
Enthusiast

Re: Formatting decimal fields while exporting

Dieter's solution works great for me but I also have negative numbers and it changes the sign. How can I work around? Thanks.

-127.84000000 becomes 127.84
Senior Apprentice

Re: Formatting decimal fields while exporting

What about -0.23000?

-.23:
TRIM(TRAILING '0' FROM col (FORMAT '-(4).9(8)')) || CASE WHEN col MOD 1 = 0 THEN '0' ELSE '' END

-0.23?
TRIM(TRAILING '0' FROM col (FORMAT '-(3)9.9(9)')) || CASE WHEN col MOD 1 = 0 THEN '0' ELSE '' END

Dieter

Re: Formatting decimal fields while exporting

Hi

I am facing an issue while loading data using Bteq.

The problem is while inserting data to table using bteq, the negative values are getting in as 0.

Please help here...

thnks

Rahul