Oracle ORA-00904标识符无效。查询在12c中工作，但在11g中不能工作

嗨，小队

我有一个相关的查询，试图在oracle 11g上执行它。同样的查询在12c上执行得很好，但它在11g上给出了ORA-00904无效标识符u.col_1。

有人能解释一下是什么导致了11g的问题吗？

选择*
从表_1 u
其中u.col_1类似“%XYZ%”
存在(选择1
来自表_2c ，
（ SELECT s.col_1来自table_2 s.col_1 WHERE s.col_1 = u.col_1 ） dd
其中c.col_1 = u.col_1
和c.col_2=dd.col_2) ；

谢谢！

您加入表2的“深度”太深。在11g中，只能在当前子查询内部/外部直接引用查询中的表。12c解除了这项限制。

要在11g中解决这个问题，您对table_1 u的引用必须是主节点的一部分。不是此中的子查询：

SQL> conn chris/chris@12c
Connected.
SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

Elapsed: 00:00:00.55
SQL> with rws as (
  2    select rownum x, 'X' dummy from dual
  3  )
  4    select * from rws r
  5    where  exists (
  6      select null from dual d, (
  7        select x, dummy from rws s where r.x = s.x
  8      ) s
  9      where  d.dummy = s.dummy
 10      and    s.x = r.x
 11    );

         X D
---------- -
         1 X

SQL> conn chris/chris@11g 
Connected.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Elapsed: 00:00:00.54
SQL> with rws as (
  2    select rownum x, 'X' dummy from dual
  3  )
  4    select * from rws r
  5    where  exists (
  6      select null from dual d, (
  7        select x, dummy from rws s where r.x = s.x
  8      ) s
  9      where  d.dummy = s.dummy
 10      and    s.x = r.x
 11    );
      select x, dummy from rws s where r.x = s.x
                                       *
ERROR at line 7:
ORA-00904: "R"."X": invalid identifier


SQL>
SQL> with rws as (
  2    select rownum x, 'X' dummy from dual
  3  )
  4    select * from rws r
  5    where  exists (
  6      select null from dual d, (
  7        select x, dummy from rws s
  8      ) s
  9      where  d.dummy = s.dummy
 10      and    s.x = r.x
 11    );

         X D
---------- -
         1 X

SQL>