How to set the empty fields in Oracle view-CodePudding

In Oracle, has built a view, view creation script is as follows:

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

Results to create a view in every field is empty, as shown in the figure below

Use your table fields of view lot. MAT_DATE is empty, the results because the TO_CHAR function, the use of oracle in view of its corresponding column identification for empty fields

Because now need to use EF in VS imported database model, this requires a view must be not empty fields, so now, so no
Note: ROWNUM column is I'd like to add a not null columns, additional results it has also been identified as can be empty...

Pray god help, how to create the empty fields in Oracle view?
Can be a new method of a non-empty field, can also be modified field is not empty method...

Note: say nothing of the NVL function method, while data can guarantee its not empty, but oracle can still identify it as empty fields

CodePudding user response:

- take a look at this for you have reference value for the

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

The create view v1 
As 
Select id 100, c1, c2 and c3 
The from (select c2, sum (c1) c1, Max (c3) c3 from t1 group by c2); 

Desc v1. 

Drop the view v1; 
Drop table t1; 

The Table created. 

SQL> SQL> 2, 3, 4, 
The View created. 

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

SQL> SQL> 
The View dropped. 

SQL> 
Table dropped. 

SQL>

CodePudding user response:

reference 1st floor wmxcn2000 response:

- take a look at this to do you have any reference value

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

The create view v1 
As 
Select id 100, c1, c2 and c3 
The from (select c2, sum (c1) c1, Max (c3) c3 from t1 group by c2); 

Desc v1. 

Drop the view v1; 
Drop table t1; 

The Table created. 

SQL> SQL> 2, 3, 4, 
The View created. 

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

SQL> SQL> 
The View dropped. 

SQL> 
Table dropped. 

SQL>

Thank you for your reply ~ but it is no use
Oracle view discriminant can empty can really rely on the reference table fields empty ~
But the original field after dealing with the certain functions, is not empty fields could become an empty fields
You give in the case of view c2 field itself is still used in

For me the most the above example, if I am the following amended

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

Will change this sentence into the following

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

Then generate corresponding field of view had actually becomes the NOT NULL

. That is to say, was not empty lot MAT_DATE after the TO_CHAR function of operation were identified as nullable field ~

So now I feel or not to change existing fields, but create a new field is not empty more feasible, only temporarily still don't know how to create a new field is not empty, but known to use Rownum is invalid