CODEWITHSUNDEEP
sqloracle-databasealter
user1356163
user1356163

Reputation: 407

using alter to add column with specific definition

how do i add a column to an existing table with a given definition. For example i have a table called employee with column called hire_date. Now using alter and only alter, i want to add a column called tenure which contains the difference between (hire_date and 14-JULY-2012). this is what ive tried

alter table employee add(tenure numeric(10) where tenure=(select to_date(
'14-JULY-2012')-HIRE_DATE from employee));

Upvotes: 0

Views: 228

Answers (2)

Thilo
Thilo

Reputation: 262504

Starting from 11g, this should work:

ALTER TABLE employee 
 ADD tenure GENERATED ALWAYS 
   AS (TO_DATE('20120714','YYYYMMDD') - hire_date) VIRTUAL

Upvotes: 1

Andomar
Andomar

Reputation: 238086

Since the new column can be derived from an existing column, I would not add it to the table. It's hard work to keep a derived column consistent, and there are better alternatives.

Instead, you could create a view that adds the column:

create view ViewName as
select  hire_date
,       hire_date - TO_DATE('20120714','YYYYMMDD') as DaysSinceJuly14th
,       ... other columns ...
from    employees

Or you could do the calculation on the fly in any select statement:

select  hire_date - TO_DATE('20120714','YYYYMMDD') as DaysSinceJuly14th
from    employees
where   name = 'Jones'

Upvotes: 1

Related Questions