Warnings for decimal fields

bi_fujitsu · Post by **bi_fujitsu** » Thu Apr 05, 2007 7:45 am

Hi All,

I am getting warnings for all the decimal fields in my job.

ORA_DISTRIBUTOR: Column OID floating point decimal is not fully supported; adjusting the scale.

I am extracting data from Oracle table(ORA_DISTRIBUTOR). Datatype and length of the OID column in Oracle table is Numeric 38 but in metadata it is showing Decimal 38 and scale 10....

How to remove this warning please suggest me...

Thanks in Advance

DSguru2B · Post by **DSguru2B** » Thu Apr 05, 2007 7:51 am

Import the metadata with orchbutil. That is more accurate. Before that, change the scale to 0 and see if that helps.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 05, 2007 7:52 am

Stop using unbounded NUMBER data types in Oracle.

What I mean here is that, if you create a column whose data type is an unqualified NUMBER in Oracle, a query for the metadata (using DESCRIBE, DataStage or any other tool) will report this as NUMBER(38).

If you create a column in Oracle with data type INTEGER (which does not exist in Oracle), a query for the metadata will report this as NUMBER(38) also.

The solution is to be very specific with the Oracle CREATE TABLE statement to create NUMBER(10) - for example - rather than just NUMBER, whether explicit or implied.

bi_fujitsu · Post by **bi_fujitsu** » Thu Apr 05, 2007 8:42 am

DSGuru,

I have changed the value of scale to 0 but it's giving the same warning.

Ray:- Can you please elaborate it, I didn't get you?

Thanks

DSguru2B · Post by **DSguru2B** » Thu Apr 05, 2007 9:22 am

What is x and y supposed to be in Decimal(x,y) ???

chulett · Post by **chulett** » Thu Apr 05, 2007 9:34 am

Precision and Scale.

DSguru2B · Post by **DSguru2B** » Thu Apr 05, 2007 9:47 am

chulett wrote:Precision and Scale.

Hehe. I meant actually the value that is supposed to be.
Is it really (38,10) ? How do you know the scale is 10 ?

dskid · Post by **dskid** » Thu Apr 05, 2007 2:17 pm

I too face the same problem alonge with this i am getting one more warning :( I am finding the count using the query in oracle stage and what should i give the data type for the count?
*Implicit conversion from source type "dfloat" to result type "int32": Possible range/precision limitation.*
*Implicit conversion from source type "decimal[38,10]" to result type "int32": Possible range/precision limitation.*
I am getting the above warnings.
Is this a harmful warning? Or can we simply ignore them or handel them?
It will be very helpful for every one - if some one tells what the data type to be defined when deriving value using query like Count Or Sum Or Max Or Level using oracle stage.
Is this is bug in Datastage - when ever i try to import metadata from oracle tables it will import all integer related columns as decimal.

us1aslam1us · Post by **us1aslam1us** » Thu Apr 05, 2007 3:14 pm

Dskid, I don't think your issue is same as the OP.

You are getting those warnings as you are trying to pass a dfloat field to int32. It is informing you about it. This can be avoided by using Modify Stage after you aggregator to convert double to integer or just create a message handler to make this warning informational.

dskid · Post by **dskid** » Thu Apr 05, 2007 3:40 pm

Thanks us1aslam1us for immediate reply,
Is there any way i can avoid these warnings rather than handling after they were generated?
:shock:

us1aslam1us · Post by **us1aslam1us** » Thu Apr 05, 2007 4:41 pm

As i said earlier use modify stage for doing an explicit conversion before sending them to target.

IHTH

ravibabu · Post by **ravibabu** » Fri May 04, 2007 7:08 am

Hi,

Set the datatype has bigint in datastage.

Ravi Babu.

jreddy · Post by **jreddy** » Fri Jul 13, 2007 9:25 am

setting the datatype as BigInt didnt get rid of the warnings..
Has anyone been able to get rid of these warnings? pls advise

Minhajuddin · Post by **Minhajuddin** » Fri Jul 13, 2007 1:42 pm

In the select query you can try this

select To_Char(col) from .........

And in the table definition in Datastage you can give it as a VARCHAR.

It always works for me

Ultramundane · Post by **Ultramundane** » Fri Jul 13, 2007 1:52 pm

jreddy wrote:setting the datatype as BigInt didnt get rid of the warnings..
Has anyone been able to get rid of these warnings? pls advise

Yes. And, that error is a big deal. I had IBM fix many corruption issues with decimals. There are several patches which can be installed to fix the issues. It is shame that IBM doesn't know how to inform customers of critical bugs in their products.