Error in Data conversion

[zam62]

A parallel job running into the following...

I have a field in a table defined as follows:
CREATE TABLE diagnosis
(diagnosis_iid NUMBER,

I have a select that is as follows:
(SELECT nvl(MAX(DIAGNOSIS_IID) ,0) LAST_VAL FROM #SCHEMA#.DIAGNOSIS)

The job contains one transformer and the field is mapped to a target field of type integer.

when I run the job, I get the following error/warning:

CONV_LD_DIAGNOSIS: When checking operator: When binding output interface field "LAST_VAL" to field "LAST_VAL": Implicit conversion from source type "decimal[38,10]" to result type "int32": Possible range/precision limitation.

Not sure why....can anyone help explain?

[ray.wurlod]

It's generated because Oracle does not have a true INTEGER data type, and converted your INTEGER specification in CREATE TABLE to NUMBER(38) or - in your particular case - because the default size of NUMBER is 38. But your job design takes this and tries to import it into an int32 (specified in your metadata).

Import the able definition using Orchestrate's orchdbutil command (via the usual Import menu) and use the data type shown there. Include an explicit conversion function (such as int32_from_decimal in a Modify stage).

[zam62]

thanks Ray for the (partial) response.

Unfortunately my company does not want Premium sponsorship so I could only see the first few sentences. Is there any other way to get a response that we non-premium dsxchangers can view?

[zam62]

Another Error I get is this:

CONV_LD_DIAGNOSIS,0: Column LAST_VAL floating point decimal is not fully supported; adjusting the scale.

f I make it an integer, I get a conversion error. If I make it a Decimal I get this error. If I make it a varchar, it will be incorrect in the DB. Is there something simple that I am missing?

[Krazykoolrohit]

suscribe to the forum. its not for your company but for your benefit.

I will post what ray is trying to say, with his permission of course.

write an explicit conversion function (such as int32_from_decimal in a Modify stage) in your job.

[zam62]

In the Database I defined the column as NUMBER. When I imported the table into DataStage, that same field gets created at decimal(38,10). Where is that translation being set, I keep getting errors/warnings on the field. How is it that Datastage 'changes' the type from Number to decimal 38,10.
Is there a setting somewhere that I can change?

[ray.wurlod]

The metadata importer has some problems. Try importing it as an Orchestrate schema definition - use the third option (from database table using orchdbutil).

[rparimi]

try CASTING the field as NUMERIC(10) in the query and in the metadata change it to Integer(10). This will help you keep it as Integer and will not convert into Decimal.