Decimal field rounding

rwierdsm · Post by **rwierdsm** » Thu Jan 05, 2006 2:28 pm

We are trying to load data into a TeraData Dec(18,0) field using a MultiLoad stage.

The source data is formated as text. We perform some deduplication and validation upstream and convert the field in question from varchar( 18 ) to Dec(18,0). When loading into TeraData, any number that is bigger than about 6 billion gets decrimented by 1.

When we try feeding the stream into a sequential file, directing the var into two fields, one with the var and the other with var + 0, the second column has a value one GREATER than the first column.

By searching the forum we have gotten ideas to try doing a number of things, including:
- int(varname)
- FIX(If (N = Int(N)) Then N Else N+1, x, y)
- Oconv(Iconv(varname, "MD0"), "MD0")
- various FMT commands

..all to no avail.

We're fresh out of ideas and would appreciate any input.

Rob Wierdsma

ArndW · Post by **ArndW** » Thu Jan 05, 2006 3:50 pm

For debugging purposes, let's stick with output to a sequential file. How do you convert the CHAR(18) to an integer in your job? I think integer values are limited to +/- 2,147,483,647 after which they are stored in floating point format. Your uvconfig will contain a setting for WIDEZERO and EXACTNUMERIC, what are they currently set to? Could you also add a 0 to your value using the builtin string math function call sadd() and see that this works correctly? Also, see what the result of In.YourBigNumString:'.00'+0 is - this might force a floating point conversion to be performed.

rwierdsm · Post by **rwierdsm** » Fri Jan 06, 2006 9:08 am

Initially, we were just taking the incoming varchar field and sticking it in a Dec(18,0) field.

Later we did some monkeying around with the functions I mentioned above.

We'll have a look at the stuff you are suggesting, I think it will generate a couple of new ideas.

Thanks for your input, Arnd.

Rob W

rwierdsm · Post by **rwierdsm** » Tue Jan 10, 2006 11:08 am

In the end, our bug turned out to be a TeraData issue.

The developer working on this piece of code loaded the input file into TeraData using a method other than DataStage and observed the same issues.
Bug was bumped to NCR support, who determined that TeraData was loading and storing the data correctly, however the SQL Assistant tool (which everyone was using to query the data) was corrupting data over Dec (16). Changed a setting and everyone was happy.

Rob Wierdsma