Oracle视图中如何设定非空字段

[yizhe2010]

在Oracle中新建了一个视图，视图创建脚本如下：

CREATE OR REPLACE FORCE VIEW MES.V_PP_YIELD
AS
SELECT ROWNUM AS ID,
lots.MatDati,
lots.DatiQty,
defs.Dati,
defs.DefQty
FROM ( SELECT TO_CHAR (lot.MAT_DATE, 'YYYY-MM-DD') AS MatDati,
SUM (lot.QTY) AS DatiQty
FROM MM_LOT lot
GROUP BY TO_CHAR (lot.MAT_DATE, 'YYYY-MM-DD')) lots
JOIN
( SELECT TO_CHAR (lot.MAT_DATE, 'YYYY-MM-DD') AS Dati,
SUM (defe.DEF_QTY) AS DefQty
FROM MM_LOT lot, LOT_DEFECT defe
WHERE lot.LOT_NO = defe.LOT_NO
GROUP BY TO_CHAR (lot.MAT_DATE, 'YYYY-MM-DD')) defs
ON lots.MatDati = defs.Dati;

结果创建出的视图各个字段都是可空的，如下图所示

视图里用到的表字段lot.MAT_DATE是非空的，结果因为TO_CHAR函数的使用，oracle在视图中将其对应的列识别为可空字段

因为现在需要在VS中用EF导入数据库模型，这要求视图中必须有非空字段，所以现在这样的话不行
备注：ROWNUM 列是我想额外加上一个非空列，结果它也被识别为可空的……

求大神相助，如何在Oracle视图中创建非空字段？
可以是新建一个非空字段的方法，也可以是修改已有字段为非空的方法……

再备注：不用说NVL函数这种方法了，虽然数据上可以保证其非空，但oracle仍然识别其为可空字段

[卖水果的net]

-- 看看这个对你有没有参考价值

SQL> create table t1(c1 int not null, c2 varchar(10) not null, c3 varchar(10) );

create view v1
as
select 100 id, c1, c2, c3
from (select c2, sum(c1) c1, max(c3) c3 from t1 group by c2) ;

desc v1;

drop view v1;
drop table t1;

Table created.

SQL> SQL>   2    3    4  
View created.

SQL> SQL>  Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID						    NUMBER
 C1						    NUMBER
 C2					   NOT NULL VARCHAR2(10)
 C3						    VARCHAR2(10)

SQL> SQL> 
View dropped.

SQL> 
Table dropped.

SQL> 

[yizhe2010]

引用 1 楼 wmxcn2000 的回复:

-- 看看这个对你有没有参考价值

SQL> create table t1(c1 int not null, c2 varchar(10) not null, c3 varchar(10) );

create view v1
as
select 100 id, c1, c2, c3
from (select c2, sum(c1) c1, max(c3) c3 from t1 group by c2) ;

desc v1;

drop view v1;
drop table t1;

Table created.

SQL> SQL>   2    3    4  
View created.

SQL> SQL>  Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID						    NUMBER
 C1						    NUMBER
 C2					   NOT NULL VARCHAR2(10)
 C3						    VARCHAR2(10)

SQL> SQL> 
View dropped.

SQL> 
Table dropped.

SQL> 

谢谢回复~不过这样是没用的 Oracle视图判别是否可空确实依赖引用表的字段是否可空~ 但原来的字段经过某些函数处理后，非空字段就可能变为可空字段 您给的例子里视图中仍然使用的是c2字段本身 针对我最上面的例子，如果我进行如下修改

(  SELECT TO_CHAR (lot.MAT_DATE, 'YYYY-MM-DD') AS MatDati,
                       SUM (lot.QTY) AS DatiQty
                  FROM MM_LOT lot
              GROUP BY TO_CHAR (lot.MAT_DATE, 'YYYY-MM-DD')) lots

将这句话修改成下面这样

(  SELECT lot.MAT_DATE AS MatDati,
                       SUM (lot.QTY) AS DatiQty
                  FROM MM_LOT lot
              GROUP BY lot.MAT_DATE) lots

那么生成的视图中对应字段确实就变成了 NOT NULL 也就是说本来非空的lot.MAT_DATE经过TO_CHAR函数的运算就被识别为可空字段~ 所以现在感觉还是不修改已有字段，而是新建一个非空新字段更可行些，只是暂时仍不知该如何新建一个非空新字段，不过已知用Rownum是无效的