From: "Aparna" <bliss_is_ignorance@hotmail.com>
Newsgroups: comp.databases.oracle.server
References: <0PbG6.12036$_W2.12062@news.indigo.ie> <9cbof3$oln$1@bob.news.rcn.net>
Subject: Re: UNION CASTING .
Date: Fri, 27 Apr 2001 21:31:10 +0800
Lines: 53
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
NNTP-Posting-Host: sbj-212-51.tm.net.my
X-NNTP-Posting-Host: sbj-212-51.tm.net.my
Message-ID: <3ae9755c.0@news.tm.net.my>
X-Trace: 27 Apr 2001 21:34:20 +0800, sbj-212-51.tm.net.my
Organization: TMnet Malaysia


oh one more though...  we cannot perform DML's on views based on unions...
the DML's can be performed only on the tables....

regards,
aparna

"Steve Long" <steven.long@erols.com> wrote in message
news:9cbof3$oln$1@bob.news.rcn.net...
> my hunch is that the constraints are applied at the table level (as they
> should be) rather than the view level.  where did you create a not null
> contraint on the "view column" ?  if you constructed something similar as
 an
> updateable view, a null value would result in a violation of the table
> constraint.  the dictionary is correct.
>
> "Maze Control - Terminal 23315 - Central 2" <trw-sinterface@pluto.gwy>
 wrote
> in message news:0PbG6.12036$_W2.12062@news.indigo.ie...
> > Can anyone help me with this ?
> >
> > Try the following SQL.
> > --create table table1 (col1 integer not null)
> > --create table table2 (col1 integer not null)
> > --create view view1 as select * from table1
> > ---union all select * from table2
> >
> >
> >
> > if you then do select column_name,nullable from user_tab_columns where
> > table_name='VIEW1'
> > the result is that col1 can contain nulls, which is incorrect.
> >
> > Try it on your own system - happens at least on Dynix and Win2000.  V817
> >
> > Does anyone know how to hack the data dictionary to force this to be
> > not-null ? (i.e. correct )
> >
> > Creating another view on top of this (AS SELECT COL1 from VIEW1  WHERE
 COL1
> > is NOT NULL )
> > does not work .
> >
> > This is a small case of a much larger production issue. The view cannot
 be
> > replaced with a table.
> > TIA !
> >
> >
> >
>
>