Datatype warnings from oracle!

dspxlearn · Post by **dspxlearn** » Tue Mar 27, 2007 3:39 am

Hi,

I am pulling data from oracle database.Some of the fields are of NUMBER datatypes in which the data is actually decimal values.
In the user defined SQL (of oracle stage),i have casted these fields to NUMBER(p,s) datatype.
Eg: From database 'col' is NUMBER
I'm changing it to -- (CAST(col as NUMBER(31,11))
and when it is passed to the datastage by giving the target field data type as decimal(31,11) the below warning is throwing up in the oracle stage's data viewer.

When binding output interface field "col" to field "col": Implicit conversion from source type "decimal[38,10]" to result type "decimal[31,11]": Possible range limitation.
What could be the possible mistake?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Mar 27, 2007 3:44 am

The warning is not from oracle but from DataStage.

Check the column formats within the job and in the stage mentioned by the warning.

dspxlearn · Post by **dspxlearn** » Tue Mar 27, 2007 3:51 am

Hi Sainath,
Thanks for the reply!

I am CASTing the datatype of fileds in the user defined sql of oracle stage.
Then passing the fields further by maintaining the target datatype as Decimal(31,11) as i did it in the sql.

The target fields datatype/length is same as what is used while Casting the fields in the oracle sql.

DSguru2B · Post by **DSguru2B** » Tue Mar 27, 2007 9:13 am

If you run that sql from your favourite sql tool, does it work?
From a glance, it seems like the warning is due to the fact that your trying to fit a 38 length field in a 31 length field. Runt he same query from TOAD or sql developer and see if you get a smooth query.

chulett · Post by **chulett** » Tue Mar 27, 2007 9:31 am

The job has no idea you are CASTing the data in the query. Fix the metadata in the stage instead. You are allowed to change it when appropriate, ya know.

mpouet · Post by **mpouet** » Tue Mar 27, 2007 9:44 am

Hi,

You describe a known bug. If you use a function in a user defined sql, decimal are seen as (38,10), and string fields are also with a length fixed by Datastage (I can't remember the length).
Just type your decimal as (38,10) and change when possible.

Matthieu

dspxlearn · Post by **dspxlearn** » Tue Mar 27, 2007 11:38 pm

Thanks all.

DSguru,
The query is working fine in the sql tools(toad and sql navigator).

chulett,
Do you mean that pre CASTed sql in oracle stage will not be recognised by the job?

Matthieu,
I tried this but no luck.I got the below warning-
Column <col> floating point decimal is not fully supported; adjusting the scale.

If i have a field defined as NUMBER (no precision,no scale) in oracle database, what should be the default value to be given in datastage?

chulett · Post by **chulett** » Wed Mar 28, 2007 12:01 am

dspxlearn wrote:chulett,
Do you mean that pre CASTed sql in oracle stage will not be recognised by the job?

No. I just mean it won't know you've done that in the sql. It will still use the metadata to drive the warnings you are seeing.

dspxlearn also wrote:If i have a field defined as NUMBER (no precision,no scale) in oracle database, what should be the default value to be given in datastage?

Doesn't matter, you've seen what it defaults to. Based on your knowledge of the data, change the metadata in the stage to match rather than casting behind the scenes. If you think it should be Decimal(38,11) then make it Decimal(38,11) in the dang stage.

Havoc · Post by **Havoc** » Wed Mar 28, 2007 1:52 am

dspxlearn wrote: If i have a field defined as NUMBER (no precision,no scale) in oracle database, what should be the default value to be given in datastage?

I faced a similar issue... You just have to change the SQL Type in your stage to Decimal(38')'.. No need to mention the Scale. This should remove the warnings.

dspxlearn · Post by **dspxlearn** » Wed Mar 28, 2007 11:44 pm

Hi Havoc,

I've tried this but warning was-
Implicit conversion from source type "decimal[38,10]" to result type "decimal[38,0]": Possible precision limitation.

I don't understand why it is showing "conversion from decimal(38,10)" .In the oracle database which is our soruce it is of NUMBER datatype(no precision).

ray.wurlod · Post by **ray.wurlod** » Thu Mar 29, 2007 5:05 am

The "why" is that, when Oracle is interrogated as to data types, it returns DECIMAL(38,10) for unbounded NUMBER types.

jreddy · Post by **jreddy** » Fri Jul 13, 2007 11:35 am

Ray, when we import the metadata through the oracle plug-in metadata import, every column in the table that has a NUMBER datatype defined is being brought over to DS metadata as decimal(38,10) and you have confirmed that..

but then from reading other topics related to this issue, i still dont get what should be done to overcome that, or rather to get rid of the warnings. I tried to manually change the column datatypes in the oracle enterprise stage, but it still throws the same error..

ray.wurlod · Post by **ray.wurlod** » Fri Jul 13, 2007 3:48 pm

Edit the metadata post-import to get it right - to coincide with what your SQL is returning.

The warning is only an alert - it doesn't necessarily mean anything bad has happened - it's alerting you to the fact that you're (theoretically at least) trying to shoehorn a larger data type into a smaller, and therefore may limit the data values with which this would succeed.

jreddy · Post by **jreddy** » Mon Jul 16, 2007 10:39 am

Thanks Ray