ORA-904 After Upgrade To Or for a statement having a Subquery

(Doc ID 1190423.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.
Oracle Database - & higher
Oracle Database - & higher


MERGE statement fails with ORA-904 after upgarding to or The same query works fine in lower releases such as or

-- Example
create table KENTYC1 (F1 INTEGER, F2 INTEGER);
create table KENTYC2 (F1 INTEGER, F2 INTEGER);
create table KENTYC3 (f1 INTEGER, f2 INTEGER);

insert into KENTYC2 values (11, 11);
insert into KENTYC2 values (21, 21);
insert into KENTYC3 values (31, 31);

select * from KENTYC1;
select * from KENTYC2;
select * from KENTYC3;

-- Below SQL works in but not in 11.2
using KENTYC2 T2
on (T3.f1 = T2.f1)
when matched then
update set t3.f2=
(select T1.F2 from KENTYC1 T1
where T1.f1=T2.f1-10)
when not matched then
insert (f1, f2) values (T2.F1,
(select T2.F2 from DUAL));

select T2.F2 from DUAL
ERROR at line 14:
ORA-00904: "T2"."F2": invalid identifier


Upgrade to or


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms