ORA-00904无效的标识符.查询适用于12c，但不适用于11g

问题描述

我有一个LINQ查询，试图在oracle 11g上执行它.相同的查询在12c上执行得很好，但在11g上给出了以下错误.

I have a LINQ query, trying to execute it on oracle 11g.The same query executes perfectly fine on 12c but it gives following error on 11g.

ORA-00904: "Extent1"."ID": invalid identifier

00904. 00000 -  "%s: invalid identifier"

*Cause:   
*Action:

以下是生成的SQL.只是显示查询中给出错误的特定部分，

Following is the generated SQL. Just showing the specific part of query which is giving error,

SELECT 
  "Extent1".ID,
  (
    SELECT "top".ADMIT_DATE
    FROM 
          ( 
            SELECT "Project7".ADMIT_DATE
              FROM ( SELECT "Extent7".ADMIT_DATE FROM SYS.Table2 "Extent7" WHERE "Extent1".ID = "Extent7".ID )"Project7"
              ORDER BY "Project7".ADMIT_DATE ASC
          )  "top"
    WHERE ( ROWNUM <= 1) AND ROWNUM <= 1
  ) 
AS C1 FROM SYS.Table1 "Extent1";

有人可以解释11g问题的根源！！该查询在12c上运行良好.

Can someone please explain what's causing the issue in 11g !! This query works fine on 12c.

更新:这是linq查询.SQL之上是这些linq查询生成的SQL的一部分.

UPDATE:Here are the linq queries.Above SQL is a part of SQL generated by these linq queries.

var query = from p in context.Person
            where p.Meetings.Any() &&
            p.Vitals.Any(v => v.WT.HasValue && v.WT >= 20 && v.WT <= 300) &&
            p.Detentions.Any(d => ((d.DX_TYPE != null && d.DX_TYPE == "09") || (d.DX_TYPE == null && "09" == null)) && d.DX.StartsWith("V") && d.DX != null) &&
            p.Meetings.Any(meet => meet.Detentions.Any(d => ((d.DX_TYPE != null && d.DX_TYPE == "09") || (d.DX_TYPE == null && "09" == null)) && d.DX.StartsWith("250") && d.DX != null))
            select new
            {
                p.HISPANIC,
                p.RACE,
                StudentID = p.PersonID,
                AdmissionOn = p.Meetings.OrderBy(meet => meet.ADMIT_DATE).Select(meet => meet.ADMIT_DATE).FirstOrDefault(),
                AdmissionYear = p.Meetings.OrderBy(meet => meet.ADMIT_DATE).Select(meet => meet.ADMIT_DATE).FirstOrDefault().Year,
                AdmissionMonth = p.Meetings.OrderBy(meet => meet.ADMIT_DATE).Select(meet => meet.ADMIT_DATE).FirstOrDefault().Month
            };

var g = query.GroupBy(k => new { Hispanic = k.HISPANIC, Race = k.RACE, AdmissionYear = k.AdmissionYear })
            .Select(k => new { Hispanic = k.Key.Hispanic, Race = k.Key.Race, AdmissionYear = k.Key.AdmissionYear, Persons = k.Count() }).ToList();

推荐答案

问题是Oracle 11g不支持引用标识符的深度超过一个查询. SQL Server(由Microsoft创建)确实支持此功能，这就是您的LINQ可以正常工作的原因，但是Oracle服务器不喜欢SQL.

The issue is that Oracle 11g doesn't support referencing identifiers more than one query deep. SQL Server (created by Microsoft) does support this, which is why your LINQ is working fine, but the Oracle server doesn't like the SQL.

从本质上讲，这是oracle提供程序工具中的一个错误，该错误不会将您的LINQ转换为11g版的有效SQL.

It is, essentially, a bug in oracle provider tools, which are not translating your LINQ into valid SQL for version 11g.

这篇关于ORA-00904无效的标识符.查询适用于12c，但不适用于11g的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！