Decimals on Oracle

boligoma · Post by **boligoma** » Tue Apr 24, 2007 3:30 pm

Hi,

I'm having trouble in insert a decimal value from SQL 2005 to Oracle 10g. I'm using DS 7.5.1.A and ODBC as a connection.

The mistake is:
[DataStage][SQL Client][ODBC][Oracle][ODBC][Ora]ORA-01722: invalid number

the field property are the same: decimal with lengh 8 and scale 2.

Using the real and the fix functions on the transformer stage it enters all the numeric (without decimal) values. In fix I'm using 2 on the scale value.

Any help would be appreciated,

DSguru2B · Post by **DSguru2B** » Tue Apr 24, 2007 3:34 pm

Specify a reject link and analyze what records are the culprits. I am suspecting that your decimal field has more than just numbers.

boligoma · Post by **boligoma** » Tue Apr 24, 2007 3:46 pm

Thanks for the quick answer. There are three records that are the culprits, this is one of them:

date(in decimal) = "25 07 86"
employeeid = 7499
custid = 104
orderid = 608
productid = 100871
quantity = 2
sales = 11.2

The charactersitics is that the sales field has a decimal value, the rest of the records are loaded quite fine. I'm using the fix(sales, 2,1) on the transformer.

Thanks in advanced,

DSguru2B · Post by **DSguru2B** » Tue Apr 24, 2007 3:59 pm

sales column looks fine. Your first column "date" seems to be the culprit. Is that truely a date field? How are you converting that and whats your target column type?

boligoma · Post by **boligoma** » Tue Apr 24, 2007 4:13 pm

I'm sorry about that... the "date" column is just a char with separate values, I had this problem before (of the timestamps) so I just wrote the dates in char's (separated with spaces) so I can transform it later.

The issue here is the sales column: it appears that the culprits the ones that has numbers at the right hand of the period. I don't know why...

Thanks,

DSguru2B · Post by **DSguru2B** » Tue Apr 24, 2007 5:28 pm

Try either of the following for the sales column

Code: Select all

Oconv(in.sales, "MR2")
OR
FMT(in.sales,"R2")