Decimal field rounding
Posted: 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
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