Oracle Enterpise Stage Warnings

gerardp · Post by **gerardp** » Fri Nov 17, 2006 6:18 am

Hi,

I've run into a slight problem/feature with the Oracle Enterpise stage. When running a union query within this stage I received the following warning relating to the column AGRMT_NUM (which is set in the stage to Integer):

Code: Select all

 oraIssue: Column AGRMT_NUM floating point decimal not fully supported.  Adjusting scale.

The column AGRMT_NUM is normally imported as an Integer and this is reflected in the imported orchestrate schema definition for the table containing this column. Non-union queries that use this column do not produce these warnings.

Therefore in order to avoid this warning I split the union query across two Oracle Enterprise stages both flowing into a Funnel and the warning disappears.

I couldn't find a post on this so hopefully it will help someone out or maybe someone knows why this warning is being produced.

Cheers,
Gerard.

ray.wurlod · Post by **ray.wurlod** » Fri Nov 17, 2006 7:53 am

Use a Message Handler to demote this warning to informational. It's basically warning you that some loss of precision in the least significant digits can occur because the floating point number being delivered is being converted to a decimal data type with a limited number of decimal places (compared to the theoretically unlimited number of decimal places that a floating point number can contain).

gerardp · Post by **gerardp** » Tue Nov 21, 2006 9:27 am

Unfortunately I'm using 7.1 - no Message Handler.

Do you know why this data type conversion is occurring in a union query and not when the same column is referenced in a non-union query?

ray.wurlod · Post by **ray.wurlod** » Tue Nov 21, 2006 12:18 pm

The data types in the result set of the UNION query should match those in the SELECT clause of the first SELECT statement in the UNION.

gerardp · Post by **gerardp** » Tue Nov 21, 2006 12:44 pm

That's the strange thing. The UNION query is 2 part and each part on its own gives a resulting data type of Integer(6) which the Oracle Enterprise stage accepts without warning. It's only when in a UNION query that the resulting data type appears to be Decimal(38,10) - hence the warning. The Oracle stage sees the resulting data type differently.

It looks like the resulting data type of an identical column in a UNION is different to when in a non-UNION query.

ray.wurlod · Post by **ray.wurlod** » Tue Nov 21, 2006 1:33 pm

Oracle has never been horribly consistent externally with numeric data types. Adjust your metadata to match what Oracle generates, and this ought to eliminate the warning. Adjust the data type explicitly in your job.

gerardp · Post by **gerardp** » Wed Feb 28, 2007 8:32 am

Just revisiting this post...

I've found that the Oracle CAST statement helps to eliminate these errors.

For example,

SELECT CAST(acc_ref AS NUMBER(13)) acc_ref FROM
(SELECT acc_ref FROM .....
UNION
SELECT acc_ref FROM .....)

Thus forcing the Oracle Enterprise stage to see the field acc_ref as it's original data type.

This also works when you have aggregate functions (SUM, COUNT etc.) where the Oracle stage also seems to assume that the result is a floating point decimal.

Gerard.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 28, 2007 3:26 pm

Useful knowledge. Thank you.

csrazdan · Post by **csrazdan** » Thu Mar 01, 2007 8:55 pm

Awesome workarround!!! It is better than mine.

This is what I have been doing for some time:
1. Add function TO_CHAR(COLUMN_NAME) in SQL Statement.
2. Adjust the datatype for the column to VARCHAR
3. In the transformer stage use StringToDecimal function to convert the datatype back to Decimal.

Thanks...