我有一个表TABLE1列COL_TAB1.
select * from TABLE1: COL_TAB1 | 1 | | 2 | | 3 |
另一个表TABLE2与COL_TAB2并引用第一列COL_TAB1
select * from TABLE2: COL_TAB2 | COL_TAB1 | val1 | 1 | val2 | 2 | val2 | 3
是否可以在table1上添加虚拟列,以便获得与以下查询相同的结果：
select
t1.COL_TAB1,(select t2.COL_TAB2 from TABLE2 t2 where t2.COL_TAB1 = t1.COL_TAB1)
from TABLE1 t1
我试过了 ：
alter table TABLE1 add (
SOME_COL_NAME as (select t2.COL_TAB2 from TABLE2 t2 where t2.COL_TAB1 = COL_TAB1)
)
但它给了我ORA-00936：缺少表情
解决方法
Oracle的
documentation on virtual columns相当清楚地说明了,你只能引用同一个表中的列;
The expression used in the virtual column definition has the following
restrictions:
- It cannot refer to another virtual column by name.
- It can only refer to columns defined in the same table.
- If it refers to a
deterministic user-defined function,it cannot be used as a
partitioning key column.- The output of the expression must be a
scalar value. It cannot return an Oracle supplied datatype,a
user-defined type,or LOB or LONG RAW.
正如@JoeStefanelli所说,做你想做的最好的选择是create a view.
