Not Null column definition problem

[harwooddale]

Hi,
I have a problem creating a view.
I have a table, lets call it table A, where all the columns are declared as not null.
I want to create a view on this table.
In the view I need to do a calculation againist 2 numeric columns (A - B). Remember the columns are defined as not null.
The resulting view doesn't include the not null against the column definition when creating the view.

example

desc tableA
-----------------

..........
column1 not null float(64)
column2 not null float(64)


create view viewa as
(select ....... (column 1 - column2) column3


desc viewa

..........
column3 float(64)

I need the column to include the NOT NULL clause so as my external application will accept the data. It only accepts data that is guaranteed to be not null.

Any help appreciated

 

[sem]

I can not understand your problem: if you need server-side validation, it will be performed. No null values can be inserted into the table your view is based on. If you need client-side validation, it's up to you to write correct procedure, regardless on whether those columns are declared as nullable or not nullable.

Regards, Dima

 

[harwooddale]

Right,
The external ERP package that we have will not accept any null values. The database that it uses has every column declared as NOT NULL.
They must have a routine that checks the definition of a column for the NOT NULL clause.
What I am tyring to do is make the view fit into the package, and not get our ERP vendor to make bespoke changes, which cost a lot of money.
Any ideas as to the view not defining the column with the not null clause.
I have looked in the SQL reference books, and the only reason I can see for this happening is if the table join was outer. As I am not using any outer joins I am baffled!!!

 

[sem]

It's not so easy to verify that function based column can not be null. Even NVL may return it . As there's no general way to check it beforehand, it's not done even for simple cases like your. BTW, I don't think you may update this view directly (instead of triggers is the only way I know), so there's no need to worry. If your ERP application is sufficiently robust, it should check USER_UPDATABLE_COLUMNS and prohibit editing at all.

Regards, Dima

 

[SantaMufasa]

Harwooddale,

Unfortunately, Oracle views do not support a NOT NULL constraint on a view "column" that derives from a function or an operation.

Isn't your ERP application smart enough to do the subtraction: "(column1 - column2)" to yield the result you need "on the fly"?

If something forces you to include the calculation in the "view" along with an explicit NOT NULL constraint, then your COST will be to create an extra TABLE (not a VIEW) that contains COLUMN3 with a NOT NULL constraint, then populate the table with the contents of TABLEA, plus the calculation, "(column1 - column2)" to populate the NOT NULL COLUMN3.

Sorry,

Dave
(Sandy, Utah, USA @ 19:52 GMT)