How do I create a mat view with zero length columns ? [message #313372] |
Fri, 11 April 2008 09:40  |
walter01
Messages: 28 Registered: April 2008
|
Junior Member |
|
|
Hi,
I'm trying to create a materialized view which has a varchar2 column in it that must be NULL.
Altough I get zero-length error messages when trying to use build-in functions I can get what I want by creating my own function that returns a NULL.
Is there a better way for achieving this ?
CREATE MATERIALIZED VIEW "BAAN"."ORDER_HEADER_walter"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT
a.T$ORNO order_no,
--nvl(NULL,NULL) alpha,
--nullif(1,1),
--NULL as leeg,
AAA(), -- My function just returns NULL. This works.
...
...
|
|
|
|
|
|
|
|
|
|
Re: How do I create a mat view with zero length columns ? [message #313424 is a reply to message #313382] |
Fri, 11 April 2008 21:36   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
skooman wrote on Sat, 12 April 2008 01:02 | If they are filled by another source, then why use an MV and not an "ordinary" table?
|
I agree. MV is a bad solution.
The CAST() function should answer your question. But I think the MV is a silly idea.
CAST(NULL AS VARCHAR2(20))
Ross Leishman
|
|
|
|