SQL ORA-00972: 标识符太长

ORA-00972: Identifier is too long

提问by Perfection

I have a query that I am executing in visual studio's query tool and I am getting the following error.

我有一个正在 Visual Studio 的查询工具中执行的查询，但出现以下错误。

ORA-00972: identifier too long.

ORA-00972: 标识符太长。

Now I believe I am aware that oracle has a 30 byte limit, but this is the query I am trying to execute.

现在我相信我知道 oracle 有 30 字节的限制，但这是我试图执行的查询。

   select 
          "cef_tsroma.tsrido" as "truck", 
          "cef_tsroma.dosvlg" as "rideNumber",
          "cef_v_cw_shipment.shipmentNumber" as "shipmentNumber"
   from 
          "cef_tsroma" left outer join "cef_v_cw_shipment" on "rideNumber" = "shipmentNumber"
   where 
          "truck" = '104490'

Unfortunately I will not be able to change the database structure itself as it is managed by another company that knows nothing about database normalization or is in a situation where they simply cannot or should not. I don't know. Do take into account that "cef_v_cw_shipment" is a view.

不幸的是，我将无法更改数据库结构本身，因为它由另一家对数据库规范化一无所知的公司管理，或者处于他们根本不能或不应该的情况。我不知道。一定要考虑到“cef_v_cw_shipment”是一个视图。

truck = '104490' is just a sample integer for testing purposes. I have attempted various solutions but the right method (or looking for the right method) seems to elude me.

Truck = '104490' 只是一个用于测试目的的样本整数。我尝试了各种解决方案，但正确的方法（或寻找正确的方法）似乎让我望而却步。

Sincerely, me.

真诚的，我。

P.S. Sorry if this is a dumb question.

PS对不起，如果这是一个愚蠢的问题。

Edit:

编辑：

select 
      "cef_tsroma"."tsrido" as "truck", 
      "cef_tsroma"."dosvlg" as "rideNumber",
      "cef_v_cw_shipment"."shipmentNumber" as "shipmentNumber"
from 
      "cef_tsroma" left outer join "cef_v_cw_shipment" on "rideNumber" = "shipmentNumber"
 where 
      "truck" = '104490'

"rideNumber" is now an invalid identifier, I will return to this shortly. I think I'm aliasing it the wrong way but I'm not sure. Goign to find out.

“rideNumber”现在是一个无效的标识符，我很快就会回到这个问题。我想我以错误的方式给它取别名，但我不确定。去了解一下。

EDIT2:

编辑2：

  select 
  ct.tsrido as "truck", 
  ct.dosvlg as "rideNumber",
  cs.shipmentNumber as "shipmentNumber"
  from  "cef_tsroma" ct
  left outer join "cef_v_cw_shipment" cs
  on "rideNumber" = "shipmentNumber"
  where  "truck" = '104490'

I am going with this syntax now as it is alot cleaner and easier to understand than the previous one. However I am still encountering ORA-00904: "rideNumber": invalid identifier (this likely counts for shipmentNumber aswell in the join line. Trying to figure this one out still, google returns naming tips: no success. Still searching.

我现在使用这个语法，因为它比前一个更清晰、更容易理解。但是我仍然遇到 ORA-00904：“rideNumber”：无效标识符（这可能对连接行中的shipmentNumber 也很重要。谷歌仍然试图弄清楚这一点，谷歌返回命名提示：没有成功。仍在搜索。

Edit3:

编辑3：

    select
            ct.tsrido as truck, 
            ct.dosvlg as rideNumber,
            cs.shipmentNumber as shipment
    from
            "cef_tsroma" ct
    left outer join 
            "cef_v_cw_shipment" cs 
    on 
            ct.dosvlg = cs.shipmentNumber
    where  
            truck = '104490'

Now following suggestions, this is the current syntax. It currently returns the error message:

现在遵循建议，这是当前的语法。它当前返回错误消息：

ERROR ORA-00904: "CS"."SHIPMENTNUMBER": invalid identifier

错误 ORA-00904: "CS"."SHIPMENTNUMBER": 无效标识符

I am sorry, I did not design this database> ):

对不起，我没有设计这个数据库>）：

Edit4/solution?

Edit4/解决方案？

This seems to work, oddly enough.

这似乎有效，奇怪的是。

    select ct."tsrido", ct."dosvlg", cs."shipmentNumber" as shipmentnumber
    from "cef_tsroma" ct 
    left outer join "cef_v_cw_shipment" cs 
    on ct."dosvlg" = cs."shipmentNumber" 
    where ct."tsrido" = '104956';

回答by John Woo

you are wrapping the whole table.column with double quotes.

你用双引号包裹整个 table.column。

select 
          "cef_tsroma"."tsrido" as "truck", 
          "cef_tsroma"."dosvlg" as "rideNumber",
          "cef_v_cw_shipment"."shipmentNumber" as "shipmentNumber"
from 
          "cef_tsroma" left outer join "cef_v_cw_shipment" on "rideNumber" = "shipmentNumber"
where 
          "truck" = '104490'

回答by wolφi

The quotes are wrong. "cef_tsroma.tsrido" should be "cef_tsroma"."tsrido"...

引号是错误的。"cef_tsroma.tsrido" 应该是 "cef_tsroma"."tsrido"...

On edit2: The new names (aliases) are not affective in the WHEREor JOINclause. To use a example, the column Xof the table DUALcan be renamed, but must be addressed with the old name in the WHEREclause:

在 edit2 上：新名称（别名）在WHEREorJOIN子句中没有影响。举个例子，X表的列DUAL可以重命名，但必须在WHERE子句中用旧名称寻址：

SELECT dummy AS "myNewName" FROM dual WHERE "myNewName" = 'X';
-- ORA-00904: "myNewName": invalid identifier

SELECT dummy AS "myNewName" FROM dual WHERE dummy = 'X';
-- X

On edit3: Your table and column names look like normal Oracle names, which are case insensitive. So you can remove all the double quotes:

在 edit3 上：您的表和列名称看起来像正常的 Oracle 名称，不区分大小写。所以你可以删除所有的双引号：

select ct.tsrido         as truck,
       ct.dosvlg         as ridenumber,
       cs.shipmentNumber as shipmentnumber
  from cef_tsroma              ct 
  left join cef_v_cw_shipment  cs on ct.dosvlg = cs.shipmentnumber
 where ct.truck = '104490';

To explain in more details: Oracle table and column names are normally case insensitive. Oracle stores them in uppercase, but you can use them lowercase, uppercase or in any combination in the query.

更详细地解释：Oracle 表名和列名通常不区分大小写。Oracle 以大写形式存储它们，但您可以在查询中使用小写、大写或任意组合。

This changes abruptly if you surround the table or column name with double quotes. Then Oracle insists on exactly that spelling.

如果用双引号将表名或列名括起来，这会突然改变。然后甲骨文坚持使用该拼写。

So, in your case, the table/view "cef_tsroma"doesn't exists, but cef_tsromaor CEF_TSROMAor "CEF_TSROMA"does...

所以，你的情况，表/视图"cef_tsroma"不存在，但cef_tsroma还是CEF_TSROMA还是"CEF_TSROMA"不...

回答by Taryn

Have you tried using aliases on the tables:

您是否尝试过在表上使用别名：

 select ct.tsrido as truck, 
      ct.dosvlg as rideNumber,
      cs.shipmentNumber as shipmentNumber
 from  cef_tsroma ct
 left outer join cef_v_cw_shipment cs
    on ct.dosvlg = cs.shipmentNumber
  where ct.tsrido = '104490'