Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2008-10-24
Medium Priority
Last Modified: 2012-05-05
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...
Question by:derdle
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 22801454

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.

Author Comment

ID: 22801512
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?

LVL 24

Accepted Solution

Joe Woodhouse earned 2000 total points
ID: 22801555
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.
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.


Author Comment

ID: 22801730
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.
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 2000 total points
ID: 22801743
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.)

Author Comment

ID: 22801866
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.

LVL 24

Expert Comment

by:Joe Woodhouse
ID: 22802052
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".  
LVL 19

Expert Comment

ID: 22805157
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.


Author Comment

ID: 22831970
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!

Author Closing Comment

ID: 31509778
Again...Joe, thank you so much for a great solution and providing the details on how to use.

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re involved with your company’s wide area network (WAN), you’ve probably heard about SD-WANs. They’re the “boy wonder” of networking, ostensibly allowing companies to replace expensive MPLS lines with low-cost Internet access. But, are they …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question