Changing datatype in materialized views

[or_dw_fan]

Hi,
I'm trying to create a materialized view v1 from two tables a &b.
Table 'a' has column acol1(varchar2(100),acol2(number).Table 'b' has column bcol1.Syntax used is
Create materialized view v1
as
select to_number(a.acol1) as matl_col
from a,b
where a.acol2=b.col1;
COLUMN OF MATL.VIEW matl_col has floating point values.After describing view v1 ,I get the datatype and data length of column matl_col as NUMBER and NULL respectively.But I like to have the data length(precision,scale) to be defined for the column matl_col.I tried to use the CAST function instead of to_number in the query. But still I get no defintion for datalength of Matl_col.To end, I need it to be number(p,s) not double or just number. Please suggest.
Thanks
or_dw_fan

[slimdave]

this may be a dumb idea, but have you tried using both cast() and to_number()

[or_dw_fan]

I tried cast and to_number together.But the same result.
Thanks

[slimdave]

You could try creating the table the mv will be based on, then creating the mv "on existing table". Might work

[slimdave]

I guess that works then, if Tom Kyte says so

http://asktom.oracle.com/pls/ask/f?p...7748575613548,

Damn it, he's always stealing my material ;-)