I seem to have come across a problem and I'm not sure how to fix it.
I've got a view in the database that references 2 tables via a 'union'. The view works properly, but right now one of the developers here needs it modified so that a certain field is 'not null'.
The problem is, even though the fields in the underlying tables are 'not null', the result of the view is still nullable.
Example, If I do the following:
create table t1
(x varchar(1) not null,
y varchar(1));
/
create table t2
(x varchar(1) not null,
y varchar(1));
/
create or replace view v1 as
select x from t1
union
select x from t2
It creates the tables properly with the x field being 'not null':
desc t1
---- -------- -----------
| X | NOT NULL VARCHAR2(1) |
| Y | VARCHAR2(1) |
However, the view leaves the X column as nullable:
desc v1
---- ---- -----------
(The X should be 'not null' in the view)
If I create a view that just accesses one table there is no problem. It seems to be the union that is messing things up.
Is there a way to force the X column to be 'not null'?
I've done some searching, and I could use NVL but I couldn't get that to work.