add virtual column in a view

user_14481599 0
Viewed 641

I want to add a virtual column age in a view.

The table is defined as:

CREATE TABLE PERSON (
, FIRST_NAME VARCHAR2(25 BYTE) 
, LAST_NAME VARCHAR2(25 BYTE) NOT NULL 
, BIRTH_DATE DATE NOT NULL 
 )

I'm creating a view with:

CREATE OR REPLACE VIEW v_person as SELECT
first_name,
last_name,
birth_date,
age (NUMBER(8,0)) generated always as(sysdate - birth_date) virtual
FROM
person;

I'm getting this error:

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
sql-view datetime oracle sql
2 Answers

A view is a virtual data structure already. The query that creates it defines its columns. Just add the expression to the select:

CREATE OR REPLACE VIEW v_person as 
SELECT
    first_name,
    last_name,
    birth_date,
    sysdate - birth_date as age
FROM person;
user_106767160

a view is a SQL which is stored in the database, when we select from the view we are actually executing the query which is defined for the view. You cannot have a virtual column in a view, you can either create the virtual column in the table or define the column in the view.

SELECT column1, column2  , (sysdate - birth_date)as age
FROM person;
user_112279190
Related