What exactly do you want to convert?
For example, what would you expect to get by trying to convert a character string like "The Source" into a DECIMAL(10) data type?
No database would let you load the raw data, but what kind of number would you expect to see?
There is a rich set of conversion functions available in DataStage, but what you're apparently trying to do is simply not a valid transformation.
Len("The Source") would return 10, the number of characters in the string, but you lose a lot of information.
The Seq() function returns the ASCII code value of a character (or the first character of a string). By itself this is unlikely to be much use to you unless you write a routine.
If the VARCHAR column in your source contains data that can validly be loaded into a DECIMAL(10) column, then theoretically you do not need to change those data. In practice performing arithmetic (such as adding zero) may be beneficial.
To be robust about it, you need to check that the source value is numeric and that it is within range for DECIMAL(10) before attempting to load it, providing a suitable default (NULL?) or rejecting the row where it is not viable.
For example:
Code: Select all
If IsNull(SourceValue) Or Not(Num(SourceValue)) Or Len(SourceValue) > 10 Then @NULL Else SourceValue