In 9.5.1 Developer client, a data viewer is run against a SQL data service and it fails with the following error reported as the output:
[LDTMCMN_0029] message [RR_4035 SQL Error [ORA-01438: value larger than specified precision allowed for this column]
The physical data object has been defined with columns defined as number(p,s) of (31,27). The source logical data object in the logical object read mapping has columns defined as decimal with precision, scale of 31,27. The corresponding virtual table related to this logical read mapping also has decimal columns defined with precision, scale of 28,27.
In 9.5.1 IDS, the precision of a decimal column of a virtual table cannot exceed 28. So, though the columns in the logical data object is defined as number(p,s) of (31,27), it will be cast to (28,27) when the SQL data service is run. If there are any data overflow with these definitions, the reported error message will be displayed.
Assume that the data in the physical data object table with the number(31,27) column is 11.11. When this is cast into decimal (28,27), the precision is insufficient to hold the data since the scale itself is 27. If you redefine the precision and scale of the virtual table as (28,26), the 11.11 data can be fit into the column.
The precision and scale of the decimal column in the virtual table should be defined correctly to hold the data being processed, keeping in mind that the precision of the decimal column cannot be set higher than 28. This needs to be followed even if the precision of the physical data object column is defined higher than 28.
(KB 153711) FAQ: What is the definition of precision and scale in a number(p,s) datatype?
What can we do to improve this information (2000 or fewer characters)