Warning message in Oracle EE stage

[snt_ds]

Column_A is defined as Decimal(31,11) in the Oracle stage(column definition)

In the SQL query, I have

Code: Select all

SELECT 
CAST( Column_A as NUMBER(31,11)) Column_A, 
Column_B 
From Table_X 
Union All 

SELECT 
CAST( Column_A as NUMBER(31,11)) Column_A, 
Column_B 
From Table_X 

When I run the job by putting this query in Oracle Enterprise Stage, I am getting the following warnings.

Table_X_Oracle: Column Column_A floating point decimal is not fully supported; adjusting the scale.
Table_X_Oracle: When checking operator: When binding output interface field "Column_A" to field "Column_A": Implicit conversion from source type "decimal[38,10]" to result type "decimal[31,11]": Possible range limitation

.

For temporarily, I am using Sub-select solution as detailed in other posts.
Is this warning related to Datastage Oracle stage or is it from the Oracle that this warning is being generated?
[/code]

[JoshGeorge]

warning related to Datastage Oracle stage

[nick.bond]

Table_X_Oracle: Column Column_A floating point decimal is not fully supported; adjusting the scale.
Table_X_Oracle: When checking operator: When binding output interface field "Column_A" to field "Column_A": Implicit conversion from source type "decimal[38,10]" to result type "decimal[31,11]": Possible range limitation

what I gather from this is that because decimal(31,11) is not full supported by the Oracle stage DS adjust the scale of the field to decimal(38,10)

Then as it moves the data from the Oracle stage onwards, it is finding that the metadata of the columns is decimal(31,11) and so it performs an implicit conversion on the data for you, throwing out a warning to the log.

You could just demote the warning message to an info, however you may end up demoting more messages that just this one so it might not be the best solution.

[thompsonp]

The warning message says that a floating point data type is not fully supported. This has been covered many times in the forum. Try searching for it. Decimal(31,11) is supported by DataStage.

DataStage reads the floating point number into a Decimal(38,10), hence not fully supporting floating point numbers.
Your job says the field is Decimal(31,11) so DataStage has to convert from Decimal(38,10) to Decimal(31,11) and warns you of possible range limitation.

Applying a function to a column in the select statement prevents DataStage determining the nullability of the field so the data type seen by DataStage will be a nullable Decimal(38,10). You may find that there is no benefit in using the CAST statement here.

[snt_ds]

thank you every one for the reply,

The actual issue is, I requested IBM for a patch since the CAST when used with Union-all is removing the precision.
IBM has replied it is more related to do with the UNION operation which is removing the precision but not the CAST field.

Could you please run the following queries and let me know if you are getting a warning for SQL 1 & SQL 2 :

Here both Column_A and Column_B are defined as Number(31,11) in the Oracle tables Table_X, Table_Y

SQL 1:

Code: Select all

SELECT 
CAST( 23.45 as NUMBER(31,11)) Column_A
From Table_X 

Union All 

SELECT 
CAST( 98.76 as NUMBER(31,11)) Column_A
From Table_Y 

SQL 2:

Code: Select all

SELECT 
Column_B
From Table_X 

Union All 

SELECT 
Column_B
From Table_Y 

Based on these results, I wanted to follow up with IBM?

thank you for the help, guys