CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle9i
Kimaya
Kimaya

Reputation: 35

Alter table add column as select statement

How to do something like this...

alter table customer_schedule add (week_number as (TO_CHAR((SCHEDULE_DATE),'iw'))

Wherein SCHEDULE_DATE is one of the existing columns in table

Upvotes: 1

Views: 17063

Answers (3)

René Nyffenegger
René Nyffenegger

Reputation: 40489

On 9i, you cannot use virtual columns, so I'd probably go with a view:

create view customer_schedule_view as
    select 
      c.*,
      to_char(c.schedule_date, 'iw')) week_number
    from
      customer_schedule c;

Of course, in your forms you need then to select from the view rather from the table.

Upvotes: 2

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

This is where you need VIRTUAL COLUMN. If you are on 11g and up, you could certainly do -

alter table table_name
add (column_name [data_type] [generated always] as (column_expression) [virtual]);

In your case, it will be something like -

alter table customer_schedule add (week_number data_type generated always as (TO_CHAR((SCHEDULE_DATE),'iw') VIRTUAL)

Upvotes: 7

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

The default keyword is supposed to let you store values when there is some value missing, but due to restrictions, you cannot use a column name

A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

I think a row-level BEFORE INSERT/UPDATE trigger should do want you want to do.

Upvotes: 0

Related Questions