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"
You can try the following
cast( (total_cost (format 'Z(n times)9.9999')) as varchar(n+6)) ;
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
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
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