data conversion problem

[Sainath.Srinivasan]

Which of those 3 values do you expect in extract ?

Look into Field() function if you wish to split.

[ETLJOB]

"39368,39399,39429" data is supposed to be falling in a single field.

Have delimiter as comma and Quote as " in the source file. You might be able to read 39368,39399,39429 in a single field using the above combination.

[hemaarvind1]

I tried the option of using comma as delimiter and quote as double , still the error persists.

In the job log, I could see the data picked up correctly from the source. But the same oracle error persists.

[Sainath.Srinivasan]

Assuming that you are referring to "Invalid Number" error, how do you expect to store the string "39368,39399,39429"into a numeric field ?

It must be a Varchar field with atleast 18 chars length long.

If you want the target to be numeric, you must pick one of the 3 values or derive a new numeric value.

[chulett]

Exactly. Saying you changed the target column in the table to a varchar and still got "the same error" makes absolutely no sense.

[hemaarvind1]

Yes, that is what is my problem.

Even if I use number or varchar at the target level, the error message returned is the same.

can you please confirm if this error could be due to comma being used in the text which is a delimiter?

[chulett]

No. We're either missing some vital piece of information or you're not doing what you are telling us you are doing. Or both.

Read the field as a string. Move it through the job as a string. Send it to a string field (VARCHAR2) in Oracle. Doing that there is no way you'll get an "invalid number" error back from the database.

[Sainath.Srinivasan]

My assumption is that you are changing the datatype in DataStage target defn and not in Oracle.

[datisaq]

Hi hemaarvind,
This kind of issues i have faced earlier also, you can solve it using the steps given below:-

1) specify the delimter as "," and quote as "none" at the format tab.
2) The field which is having this issue, go to it's field level properties by double clicking on the column.Then specify the delimter as "," and quote as "double"

This will resolve your issue definitely.

Regards
datisaq

[chulett]

They don't seem to be having any issues actually reading the value from the file, the disconnect seems to be in how they expect to store that in the target table. Even though it has been stated multiple times here already, I'll go through it one more time.

* If you want to store "39368,39399,39429" just like it looks, commas and all - you need to be targeting a VARCHAR2 field in Oracle and pass it through your job as a string.

* If you want to store as a single number, your only choice here is to remove the commas and then use StringToDecimal() to convert it to 393683939939429 and store it in a NUMBER field in Oracle. One with at least a precision of 15.

[hemaarvind1]

Hi all,

I finally got this resolved. I have defined the datatype as varchar and "extended" as "unicode" in both source and target stages and it worked.

Thank you all for your valuable inputs.

[devesh_ssingh]

good to go green(resolved) :D

[chulett]

Still unclear what the data type of your actual target field in the Oracle table was for this field. Are you saying you were able to load this to some kind of NUMBER field by calling it "unicode"?

[rameshkm]

Hi,
In Sequential file on format tab in Quote mark as Comma(,).