In Oracle 9.2.0.1.0 we observe that the precision and scale are lost when doing a CAST in a view that contains an inline component (with the casting in the inner part)
When the underlying table contains a field with number(10,2)
then a describe on the view results in datatype number.
Version 9.2.0.5.0 appears to handle this much better, yielding the correct precision and scale.
However with a very complex view (2 selects deep with numerous joins) precision and scale are lost for any field with a datatype like number(10,2) or any other combination.
The inner part contains a great many calculated fields like:
The proper thing would be to apply the casting within the outer select, but I am still curious why this would occur.
The same version of the view on SQL server 2000 maintains the correct scale for all calculated columns but sets precision to 38 (a max?)
Has anyone observed this as well?
Ties Blom
When the underlying table contains a field with number(10,2)
then a describe on the view results in datatype number.
Version 9.2.0.5.0 appears to handle this much better, yielding the correct precision and scale.
However with a very complex view (2 selects deep with numerous joins) precision and scale are lost for any field with a datatype like number(10,2) or any other combination.
The inner part contains a great many calculated fields like:
Code:
SUM(CASE WHEN X = 'PF15' THEN Y END) AS PF15
OR:
CAST(SUM(CASE WHEN rapp_eenheid= 'PF15' THEN rapp_waarde END) AS NUMBER(10,2)) AS PF15The proper thing would be to apply the casting within the outer select, but I am still curious why this would occur.
The same version of the view on SQL server 2000 maintains the correct scale for all calculated columns but sets precision to 38 (a max?)
Has anyone observed this as well?
Ties Blom
