All Forums UDA
Chaitali 3 posts Joined 07/09
14 Jul 2009
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 .00.0320 .0320.6654 .66540.1000 .199.5670 99.567I 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

Adeel Chaudhry 773 posts Joined 04/08
14 Jul 2009

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.0000HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Chaitali 3 posts Joined 07/09
14 Jul 2009

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.0010SELECT CAST('99.001' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 99.0010SELECT CAST('0.0000' AS DECIMAL(7,4) FORMAT 'Z9,9999'); --> 0.0000My 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

Adeel Chaudhry 773 posts Joined 04/08
14 Jul 2009

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

-- If you are stuck at something .... consider it an opportunity to think anew.

dnoeth 4628 posts Joined 11/04
14 Jul 2009

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

Dieter

Chaitali 3 posts Joined 07/09
14 Jul 2009

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

Adeel Chaudhry 773 posts Joined 04/08
14 Jul 2009

Nice Dieter! :)

-- If you are stuck at something .... consider it an opportunity to think anew.

logc 34 posts Joined 09/05
23 Mar 2011

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

dnoeth 4628 posts Joined 11/04
23 Mar 2011

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

Dieter

RahulVerma 1 post Joined 05/11
05 Apr 2012

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

MaheshJessy 26 posts Joined 12/10
03 Mar 2015

Hi Team, Can you help me as I have a column QTY which has decimal (10,4) data type and output follows
Actual Output:-
2.0000
1.0000
But we would like to have below mentioned output for same.
Required Output
00000000020000+
00000000010000+
Would you please help us
Regards,
Mahesh

david.craig 73 posts Joined 05/13
03 Mar 2015

Try the following:

sel 2.0000 (format '9(10)V9(4)+');
 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.
         2.0000
---------------
00000000020000+
 
See the FORMAT phrase in the data types and literals reference for details.

You must sign in to leave a comment.