help with converting varchar to decimal

[ranga1970]

I have a source as a varchar and target as decimal i am using CONVERT(Decimal(10), "The Source"), but it is not of much help job is trowing warning could some one suggest please

[rasi]

Hi

The convert function is to convert list of characters strings to new string. It is a search and replace function and not to be used for converting numeric values.

You have to give more detail in your question. Source, Target ?..

If you source is sequential file then read the source as varchar and for the target column create the metadata data as Decimal(10) and use a trim function. Without any transformation this should work if your source data is valid for the metadata you created in the target.

You have functions like Iconv which can be used to conversion. Have a look in the help file for the list of options available

Thanks
Siva

[ray.wurlod]

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