Re: UNION CASTING .

I didn't create a null constraint on the view. The sql you see below is all that is needed.  Please try the SQL below and tell me what your instance returns from the view column type.

I am of the opinion that the view should be not null as it is a union of two not-nulls but Oracle does not seem to think so. A (NOT_NULL + a NOT_NULL) can't return NULLs , I would have thought.

"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 - 07:54:56 CDT