Search code examples
sqldatabaseoracle-databaseoracle9i

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

Solution

  • 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)