How could I default a date column as 21 days ahead of another date column in oracle?

user_14693991 0
Viewed 54

Here is my table (with the working columns including primary key removed for simplicity)

 CREATE TABLE loans
    (checkout_date DATE,
     due_date DATE DEFAULT 'checkout_date' + 21,
     )

This code returns "ORA-00932: inconsistent datatypes: expected DATE got NUMBER". With '21' instead returns the same error. Any help is appreciated!

create-table datetime oracle sql
1 Answers

I don't think that Oracle supports setting a default from another column. One workaround would be to use a trigger, but that might be overkill here. Why not just put the logic in a view?

create view v_loans as 
select checkout_date, coalesce(due_date, checkout_date + 21) as due_date from loans

Or we can get a little fancy with a computed column:

create table loans (
    checkout_date date,
    due_date      date,
    real_due_date date as (coalesce(due_date, checkout_date + 21))
);

Then you would use column real_due_date instead of due_date in your queries.

For the whole thing to make more sense, you would probably need a not null constraint on column checkout_date.

Side note: as commented by mathghy and Thorsten Kettner, this does not strictly implements the concept of DEFAULT, because, even if a NULL value is explictely assign to the column, the view (and the computed column) would still apply the replacement logic (which a DEFAULT would not do).

user_106767160
Related