want to capture data which is causing error

tanaya.deshpande@tieto.co · Mon Aug 30, 2010 12:04 am

In my job 1 row(or many ) is giving implicit conversion error .

how can I find the row which is causing trouble in thousand and millions of rows.The column which is having the error is not any key,can accept nulls and has datatype float.

anand_dafaria · Post by **anand_dafaria** » Mon Aug 30, 2010 12:20 am

Please put the exact error emssage...

anand_dafaria · Post by **anand_dafaria** » Mon Aug 30, 2010 12:24 am

What is the target column data type you are putting your source column into and in which stage this error is coming? For checking which value is causing the error you can try a sample job by restricting the input rows to 10 or 20 to check it.

tanaya.deshpande@tieto.co · Mon Aug 30, 2010 12:33 am

There is no conversion.it is simple mapping of column from oracle sourse to
sqlserver target. I have not done any null handling.

this is the warning :

Copy_of_MAXIMO_WORKORDER: When checking operator: When binding output interface field "ABBESTDOWNTIME" to field "ABBESTDOWNTIME": Implicit conversion from source type "dfloat" to result type "sfloat": Possible range/precision limitation. [api\interface_rep.C:6183]

this is the error:

Copy_of_MAXIMO_WORKORDER,0: (aptoci.C:460). Message: ORA-01438: value larger than specified precision allowed for this column
[aptoci.C:1011]

I have checked with changing the datatype of column from float to double but it does not work

ray.wurlod · Post by **ray.wurlod** » Mon Aug 30, 2010 12:49 am

Did you change it in Oracle? In Maximo?

tanaya.deshpande@tieto.co · Mon Aug 30, 2010 12:52 am

I changed datatype from float to double in both oracle source as well as sqlserver target

nani0907 · Post by **nani0907** » Mon Aug 30, 2010 12:54 am

what is the data type used for this column in oracle source stage field "ABBESTDOWNTIME".

tanaya.deshpande@tieto.co · Mon Aug 30, 2010 12:55 am

float

nani0907 · Post by **nani0907** » Mon Aug 30, 2010 1:09 am

in query by cast function ,convert it as varchar like this cast(Trim(ABBESTDOWNTIME) as varchar

and while inserting it into sql server again convert it as target table column data type

anand_dafaria · Post by **anand_dafaria** » Mon Aug 30, 2010 1:22 am

Try putting a modify stage in between the source and target. Keep the source column datatype as dfloat and the target datatype as sfloat (as required by target SQL server). Datastage does an implicit conversion from dfloat to sfloat. Give it a try.

tanaya.deshpande@tieto.co · Mon Aug 30, 2010 2:42 am

Modify stage does not work...
this is what i gave in the specification

ABBESTDOWNTIME:sfloat=ABBESTDOWNTIME

is this correct

tanaya.deshpande@tieto.co · Mon Aug 30, 2010 2:42 am

the cast function also does not work
cast as varchardoes not work

anand_dafaria · Post by **anand_dafaria** » Mon Aug 30, 2010 2:50 am

I dont have server connection right now, otherwise would have checked at my end instead of hit and trial. The modify specification is wrong.

Just map it directly and let datastage does the automatic conversion.
ABBESTDOWNTIME=ABBESTDOWNTIME

tanaya.deshpande@tieto.co · Mon Aug 30, 2010 4:43 am

I have tried to use modify stage but it does not work

I am still getting this warning

Filter_61: When checking operator: When binding output schema variable "outrec": When binding output interface field "ABBESTDOWNTIME" to field "ABBESTDOWNTIME": Implicit conversion from source type "dfloat" to result type "sfloat": Possible range/precision limitation.

tanaya.deshpande@tieto.co · Mon Aug 30, 2010 6:26 am

does anyone know how i can find the row or rows which are giving errors