This discussion is archived
2 Replies Latest reply: Sep 29, 2013 1:34 PM by rp0428 RSS

ORA-00936: missing expression when adding virtual column

adf009 Explorer
Currently Being Moderated

hi am having this error when adding virtual column am in database 11g

am having error

ORA-00936: missing expression

 

from select upper

 

my sql is

ALTER TABLE employees ADD (username AS(select upper(substr(first_name,1,1)) || initcap(last_name)username from employees));

  • 1. Re: ORA-00936: missing expression when adding virtual column
    Etbin Guru
    Currently Being Moderated

    http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#sthref3374

     

    Adding a Virtual Table Column: Example The following statement adds to a copy of the hr.employees table a column named income, which is a combination of salary plus commission. Both salary and commission are NUMBER columns, so the database creates the virtual column as a NUMBER column even though the data type is not specified in the statement:

    CREATE TABLE emp2 AS SELECT * FROM employees; ALTER TABLE emp2 ADD (income AS (salary + (salary*commission_pct)));

     

    ALTER TABLE employees ADD (username AS(upper(substr(first_name,1,1)) || initcap(last_name)));


    Regards


    Etbin

  • 2. Re: ORA-00936: missing expression when adding virtual column
    rp0428 Guru
    Currently Being Moderated

    hi am having this error when adding virtual column am in database 11g

    am having error

    ORA-00936: missing expression

     

    from select upper

     

    my sql is

    ALTER TABLE employees ADD (username AS(select upper(substr(first_name,1,1)) || initcap(last_name)username from employees));

    You can't use a SELECT statement in the virtual column definition and you don't need to anyway. Get rid of the SELECT and everything except the UPPER function declaration. This works just fine for me:

     

     

    create table emp_test (first_name varchar2(30), last_name varchar2(30));

    ALTER TABLE emp_test  ADD (username AS (upper(substr(first_name,1,1)) || initcap(last_name)));

     

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points