Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to define column types of a materialized view

Re: How to define column types of a materialized view

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: 27 Sep 2001 03:37:54 -0700
Message-ID: <a20d28ee.0109270237.2146d98a@posting.google.com>


Dino Hsu <dino1.nospam_at_ms1.hinet.net> wrote in message news:<2vg5rtc6t6i9vu0ag6r7k78v86sal1b4vq_at_4ax.com>...
> Dear all,
>
> I created a materialized view from a base table:
>
> SQL> desc tb_orders
> Name Null? Type
> ----------------------------- -------- --------------------
> INVOICE_DATE NOT NULL DATE
> INVOICE_NO NOT NULL CHAR(5)
> SEQ_NO NOT NULL NUMBER(3)
> REP_NO NOT NULL VARCHAR2(8)
> FSC VARCHAR2(9)
> POC NOT NULL VARCHAR2(5)
> PRICE_YEAR NOT NULL NUMBER(4)
> PRICE_CAMP NOT NULL NUMBER(2)
> OP_YEAR NOT NULL NUMBER(4)
> OP_CAMP NOT NULL NUMBER(2)
> CUSTOMER_SERVED NUMBER(3)
> ORDER_SOURCE CHAR(1)
> FOR_NO NUMBER(2)
> SELLING_PRICE NOT NULL NUMBER(7)
> UNITS NUMBER(10)
> NTA NUMBER(15,3)
> UNITS_OF_RETURN NUMBER(10)
> NTA_OF_RETURN NUMBER(15,3)
> COST NUMBER(13,3)
> PROCESS_DATE NOT NULL DATE
>
> CREATE MATERIALIZED VIEW mv_fsr_base AS
> SELECT
> SF_MAKE_YEAR_CAMP(op_year, op_camp) op_time,
> SUBSTR(invoice_no,1,2) invoice_prefix,
> fsc,
> SUM(units) units,
> SUM(nta) nta
> FROM
> tb_orders
> GROUP BY
> op_year,
> op_camp,
> SUBSTR(invoice_no,1,2),
> fsc;
>
> SQL> desc mv_fsr_base
> Name Null? Type
> ----------------------------- -------- --------------------
> OP_TIME VARCHAR2(4000)
> INVOICE_PREFIX VARCHAR2(4)
> FSC VARCHAR2(9)
> UNITS NUMBER
> NTA NUMBER
>
> Note that the OP_TIME column of the materialized view is of
> VARCHAR2(4000) but it should be VARCHAR2(8) or CHAR(8), the
> INVOICE_PREFIX is VARCHAR2(4), it should be VARCHAR2(2) or CHAR(2),
> how do you manage data types?
>
> Thanks,
> Dino

Not,

This is a 'feature' which exists quite a long time. You can't specify any datatype in the CREATE statement (don't remember the exact error number, but try it, and you'll see), so I think you're stuck on this one, other than getting an explanation for this behavior from Support.

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Thu Sep 27 2001 - 05:37:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US