How to create a non-null column in a view

Question

Given a type table:

CREATE TABLE "MyTable" ( "MyColumn" NUMBER NOT NULL ); 

I want to create a view like:

 CREATE VIEW "MyView" AS SELECT CAST("MyColumn" AS BINARY_DOUBLE) AS "MyColumn" FROM "MyTable"; 

Only where the "MyColumn" column is "NOT NULL".

In SQL Server, this is pretty straight forward:

 CREATE VIEW [MyView] AS SELECT ISNULL(CAST([MyColumn] AS Float), 0.0) AS [MyColumn] FROM [MyTable]; 

However, the Oracle equivalent results in a "NULL" column:

 CREATE VIEW "MyView" AS SELECT NVL(CAST("MyColumn" AS BINARY_DOUBLE), 0.0) AS "MyColumn" FROM "MyTable"; 

Is it possible to force Oracle to mark the view column as "NOT NULL" in the metadata?

Answer 1

You cannot add a non-empty or control constraint to the view; see this and on the same page "Constraints on NOT NULL Constraints" and "Constraints on control constraints". You can add with check option to the view (against the offer of a redundant offer), but it will not be marked as not null in the data dictionary.

The only way I can get this effect is if you are at 11g, add the cast value as a virtual column in the table and (if you still need it) create a view against this:

 ALTER TABLE "MyTable" ADD "MyBDColumn" AS (CAST("MyColumn" AS BINARY_DOUBLE)) NOT NULL; CREATE OR REPLACE VIEW "MyView" AS SELECT "MyBDColumn" AS "MyColumn" FROM "MyTable"; desc "MyView" Name Null? Type ----------------------------------------- -------- ---------------------------- MyColumn NOT NULL BINARY_DOUBLE 

---

Since you said in a comment on dba.se that this is to mock something, you can use a regular column and trigger to simulate a virtual column:

 CREATE TABLE "MyTable" ( "MyColumn" NUMBER NOT NULL, "MyBDColumn" BINARY_DOUBLE NOT NULL ); CREATE TRIGGER "MyTrigger" before update or insert on "MyTable" FOR EACH ROW BEGIN :new."MyBDColumn" := :new."MyColumn"; END; / CREATE VIEW "MyView" AS SELECT "MyBDColumn" AS "MyColumn" FROM "MyTable"; INSERT INTO "MyTable" ("MyColumn") values (2); SELECT * FROM "MyView"; MyColumn ---------- 2.0E+000 

And desc "MyView" still gives:

  Name Null? Type ----------------------------------------- -------- ---------------------------- MyColumn NOT NULL BINARY_DOUBLE 

As Lei mentioned (also on dba.se), if you want to insert / update a view, you can use the instead of trigger with a VC or a fake version.

Answer 2

If you could have a NOT NULL constraint in the view column, I believe that the SELECT from the view will then fail if the column in question was NULL. If this is the intention, then the following may give you what you are looking for:

 CREATE OR REPLACE VIEW some_view AS SELECT some_field, some_other_field, CASE WHEN field_of_interest IS NOT NULL THEN CAST(field_of_interest AS BINARY_DOUBLE) ELSE 1 / 0 END AS field_of_interest_not_null FROM some_table; 

Not very attractive, and you get the ugly message "ORA-01476: division is zero" if the ELSE CASE branch is selected, but perhaps this is a step on the road to "better."

Share and enjoy.

---

EDIT: If the goal is to only collect rows where your target column is not null, perhaps you could add a WHERE clause to your view, as in:

 CREATE OR REPLACE VIEW some_view AS SELECT some_field, some_other_field, CAST(field_of_interest AS BINARY_DOUBLE) AS field_of_interest FROM some_table WHERE field_of_interest IS NOT NULL; 

YMMV.

---

EDIT2: Looking at the SQL Server example, it seems that the ISNULL function is used to ensure that the column is never NULL. If this is acceptable, you can do the following:

 CREATE OR REPLACE VIEW some_view AS SELECT some_field, some_other_field, CAST(NVL(field_of_interest, 0.0) AS BINARY_DOUBLE) AS field_of_interest FROM some_table WHERE field_of_interest IS NOT NULL; 

To quote Bullwinkle: “This time sure!” :-)