How to format a decimal column in Fast Export

Database
Enthusiast

How to format a decimal column in Fast Export

Hi,

I have used Fast export script to export data to text file where one of the column has decimal values. I have tries the below to cast it 

1) cast(sum(total_cost)as char(21))   and when the sum value is 0 the output in text file is ".0000"

2) cast(sum(total_cost)as decimal(21,4)) and this gives me junk characters.

Please just me if there are any other alternatives so that I would get the ouput as below

 when sum is 0 it should be displayed as  "0.0000" and when there is a sum 345.178 it should be displayed as "345.1780"

6 REPLIES
Enthusiast

Re: How to format a decimal column in Fast Export

Hi,

You can try the  following

cast( (total_cost  (format 'Z(n times)9.9999')) as varchar(n+6)) ;

here

n - depends on the size defined

say for example if you have defined total_cost as decimal(18,2) then n = 18-1 = 17

in the above case the sql stmtn would be

cast( (total_cost  (format 'Z(17)9.9999')) as varchar(23)) ;

please let me know if this works

Enthusiast

Re: How to format a decimal column in Fast Export

Hi Rajeev,

It's working...

Thank you :)

Enthusiast

Re: How to format a decimal column in Fast Export

welcome :)

Enthusiast

Re: How to format a decimal column in Fast Export

Hi Rajeev,

Can you suggest me the fast export script such that I can export the data into an excel?

Enthusiast

Re: How to format a decimal column in Fast Export

Hi Chaitanya,

If you are running the Fast export in UNIX then you can try creating the output

file a comma seperated one with a .csv extention

i.e have "," as your delemiter

ex: abcdef,hijklmno,wer

and then when you move the file back to windows it will open in excel and the fields will be split  based on the delimiter (",")

Please let me know if this helps

Enthusiast

Re: How to format a decimal column in Fast Export

Thanks Rajeev