Solved

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

Posted on 2008-10-24
10
1,876 Views
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...
0
Comment
Question by:derdle
  • 5
  • 4
10 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 22801454
Aha!

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.
0
 

Author Comment

by:derdle
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 places...so 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?

0
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 500 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.
0
 

Author Comment

by:derdle
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.
0
 
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 500 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
AS
SELECT A, B, "C"=convert(numeric(30,6), C), D
FROM [table]
go


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.)
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:derdle
ID: 22801866
So this isn't actually creating a table...cool.   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.

0
 
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".  
0
 
LVL 19

Expert Comment

by:grant300
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.

Regards,
Bill
0
 

Author Comment

by:derdle
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!
0
 

Author Closing Comment

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In this article, I will show you HOW TO: Suppress Configuration Issues and Warnings Alert displayed in Summary status for ESXi 6.5 after enabling SSH or ESXi Shell.
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now