Thanks for the question, v.
Asked: February 07, 2003 - 3:04 pm UTC
Last updated: August 14, 2004 - 1:55 pm UTC
Version: 8i
Viewed 1000+ times
You Asked
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
and Tom said...
build it on a prebuilt table:
ops$tkyte@ORA817DEV> create table t1 ( a int primary key, x varchar2(5) );
Table created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> insert into t1 values ( 1, '1' );
1 row created.
ops$tkyte@ORA817DEV> insert into t1 values ( 2, '2' );
1 row created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create table t2 ( matl_col number(5,2) );
Table created.
ops$tkyte@ORA817DEV> insert into t2 select to_number(x) from t1;
2 rows created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create materialized view t2
2 ON PREBUILT TABLE
3 WITH REDUCED PRECISION
4 as
5 select to_number(x) matl_col from t1;
Materialized view created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> desc t2;
Name Null? Type
----------------------------------- -------- ------------------------
MATL_COL NUMBER(5,2)
ops$tkyte@ORA817DEV>
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment