Changing datatype in materialized views
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Changing datatype in materialized views

  1. #1
    Join Date
    Feb 2003
    Posts
    3

    Changing datatype in materialized views

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    this may be a dumb idea, but have you tried using both cast() and to_number()
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2003
    Posts
    3
    I tried cast and to_number together.But the same result.
    Thanks

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You could try creating the table the mv will be based on, then creating the mv "on existing table". Might work
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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 ;-)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width