Sybase bcp is changing real number format, how do I prevent this?

I'm running Sybase 11.9.2 and I am running the following bcp command:

bcp site1.ff.pricing out pricefile_tmp.out -c -t "\t" -r "\r\n" -Uff -Pxxxx

The pricing table being dumped has multiple columns formatted as "real".   In the output file created by bcp, those values no longer match what is in the database, as shown below.

Value in Table           Value in bcp output
2.181200                    2.1812000274658203
2.775000                    2.7750000953674316

Why is this occurring, and how do I stop it.  I need to get the values to come out just as they are in the table.

Thanks all...
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joe WoodhousePrincipal ConsultantCommented:

Actually they *are* the same as what's in the table. If you're using "isql" or some other client tool to display the rows in the table, isql itself is silently truncating some of the display. bcp can correctly produced what's output in the table.

You can test this with something like this:

SELECT convert(varchar(32), convert(numeric(30,16), [column]))
FROM    [table]

If the data really was stored only to 6 decimal places, then this query would pad everything else with zeroes after that. I'm pretty certain you'll see something similar to what bcp is producing. (The final convert to varchar is so isql doesn't reinterpret the numeric format and start truncating display again).

Note this is not a truncation of *value*, only of what's displayed.

You have an issue here - anything your database calculates is based on the full untruncated value of this data, so producing a truncated version with bcp may produce something that *looks* identical to what you normally see, but will not in fact be an identical value and so would produce different results.

What's more important to you here?

It's easy enough to produce a bcp file to only six decimal places for this column, but I think you think to think more about whether you want to truncate or round, and whether it makes sense to throw precision away.

Lastly, "real" is a poor choice for any numeric data in Sybase ASE because it is highly platform dependent, and it is not precise. You can't predict exactly what it's values will be. A "numeric" with suitable precision is a much better choice - the precision is exact, predictable and will work the same on every platform.
derdleAuthor Commented:
Yea, I am not a fan of "real" types as I have found what you indicated in other instances.  I guess that I have never noticed the discrepancy (for lack of a better work, since we're saying it's not really) before, since the difference is so small, it has never affected the outcome of any calculations, etc.  Oddly enough though, I have looked at the source from which the table gets populated, and values appear to be going in (being loaded) to the table with only six decimal not sure how/when they started getting the extra digits (maybe during some calculation-update?).

Given your explanation and the intended use of the data (to be provided to a source to match reports, which showed only six places), I need to truncate.  How would I get bcp to do that?  I'll need to also get bcp to dump them with five decimals at some point for an export into another system, that only supports five - I'm guessing it's the same process?

Joe WoodhousePrincipal ConsultantCommented:
Even if you only load six decimal places, the "real" type might add some, since it is not an exact datatype.

Probably your easiest method here is to create a view that contains a convert to (say) numeric(x, 6) (pick enough digits), and then bcp out of the view. You could have a different view for 5 digits.

It's been possible to bcp out of a view since Open Client 11.1. It's a nice trick.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

derdleAuthor Commented:
Can't say that I have ever done that (created a view, let alone bcp from one)...I'll go start digging on how to do it, unless you can point me some place, or if not a lengthy chore, let me know.  I don't mind digging for info though...not trying to be lazy.  Glad to know there's a pretty straight forward way to do it.
Joe WoodhousePrincipal ConsultantCommented:
Let's say your table has columns A, B, C and D, and C is the one we need to truncate.

CREATE VIEW bcp_view
SELECT A, B, "C"=convert(numeric(30,6), C), D
FROM [table]

Then from the command line you bcp out of this as normal as though it were a table. Very simple!

(I've assumed you won't have more than 14 digits to the left of the decimal point.)
derdleAuthor Commented:
So this isn't actually creating a   How long does the "view" remain?  Do you "DROP VIEW" to get rid of it?   Is the primary intention of a view to let you work with "displayed" data versus stored?  I mean I could create a table this same way....although I guess then I would still be stuck with how Sybase stores it as I am now, and get the same results, versus getting the viewed data format that I need?  Guess I have my reading for the weekend.  LOL.

Will report back as soon as I give this a try.

Joe WoodhousePrincipal ConsultantCommented:
It's a persistent object, so the view remains until it's dropped by, you guessed it DROP VIEW.

I think of views as "frozen SELECT statements" - they present the resultset of a SELECT as though it were in a table. Most but not all things legal in a SELECT can be part of a VIEW. In some scenarios you can write through the view but many (including the one I'm suggesting for you) will be read only.

It is precisely because they present as a table (and you can use a view anywhere you'd normally use a table) that lets us bcp out of the view.

Ultimately your best solution would be to not use a "real" datatype. If you'd used some kind of "numeric" you'd have exact precision and none of this sneaky "we'll calculate to 12 places but only show you 6".  
Joe is really right on this one.  Your plan on truncating will NOT give you the same results in a report prepared in a different venue.

If you are running reports that have used data from Sybase, "matching" the reports using either the full precision of the real or your truncated version may be problematic.  It all depends on what the reporting tools is doing.  If it is using the full precision internally, you want to do the same thing in your extract.  If it is rounding or truncating to some level, you would need to match that as well.  If it is doing math inside the database then you really need to leave the data in full precision.  On the other hand, if the report is doing math in the reporting tool, you are again faced with trying to figure out how it is done and trying to match it in whatever auditing tool you are going to build.  If your reporting tool is doing math and runs on a different platform, you are probably hosed for an exact match on a real no matter what you do.

All of this is why the MONEY datatype exists.  It is essentially a 64 bit integer with a four decimal digit (100th of a penny) fixed offset.  This allows for precise monetary calculations with predictable and insiginificant roundoff errors.  Decimal data types are essentailly the same thing except they can store an arbitrary number of digits (within reason) and an arbitrary number of decimal places to the right.

Your best bet is to take the data as it comes out of the database and go ahead and do your auditing.  You can then explain that the noise is a result of the database using the wrong datatype.  Any other approach leave you open to having modified things in such a way that you are responsible for the descrepencies and not whomever designed the system in the first place.

derdleAuthor Commented:
Yea, had we been responsible for creating the architecture we would not have used "real", as it definitely seems to cause (or has the potential) various issues of this type, when there are other more accurate/reliable ways.  ...and completely understood on the points of "matching" the data...fortunately, we're matching to reports that are created from the import source, so we should not run into any problems with where the math is being done, etc.   .....keeps fingers crossed.  =)

As for the solution of using a VIEW.....awesome!!   This worked perfect, and I am already considering other areas where-as I can put this to use.  Love learning something new.  

Thanks so much guys!
derdleAuthor Commented:
Again...Joe, thank you so much for a great solution and providing the details on how to use.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.