Re: UNION CASTING .

hi steve,

ur reasoning makes sense... however if u try the following

create table table1 (col1 integer not null) create view v1 as select * from table1

then the query

select column_name, nullable
from user_tab_columns
where table_name= 'VIEW1'

shows nullable as N for view1

but if u create a view with a join, nullable is displayed as Y... any ideas why????

however what u say abt the view being updated is true... oracle will *not* allow us to insert any values into the view that violate the constraint on the tables on which the view is based... constraints r given on tables not views...

regards,
aparna

"Steve Long" <steven.long_at_erols.com> wrote in message news:9cbof3$oln$1_at_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_at_pluto.gwy>
 wrote
> in message news:0PbG6.12036$_W2.12062_at_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 !
> >
> >
> >
>
>
Received on Fri Apr 27 2001 - 08:24:20 CDT