Decimal Validation

[vskr72]

I have a oracle target table column with data type as Decimal and its precision is 12,3. SO, it can accomodate 3 digits after the decimal.

I have an input coming from a flat file with the following values :

Code: Select all

100
200.1
300.123
10A

Code: Select all

IsValid("decimal[12,3]", SrcCol) then SrcCol else SetNull()
 

I was expecting the 100, 200.1 and 300.123 to be treated as valid decimals and 10A to be treated (set it to NULL). I don't get the desired results. Am I missing anything here? Thank you

[chulett]

What results do you get?

[vskr72]

All the values are set to 0.000.

[pandeesh]

i believe, since the input records are not satisfying decimal[12,3] ,it leads to 0.000.
Can you please check for decimal without scale and precision(unbounded) in IsValid?( i am not sure)

Code: Select all

IsValid("decimal", SrcCol) then SrcCol else SetNull() 

Please try this!

[pandeesh]

One more thought is bring the input value to decimal[12,3] using left() and right() formatting.Then go with isValid().

[chulett]

All the values are set to 0.000.

That shouldn't have anything to do with your validation check, all that does is either pass along the original value if it is deemed valid or replace it with a null. Look elsewhere for your "all are set to zero" issue.