ORA-00972:标识符太长 [英] ORA-00972: Identifier is too long

问题描述

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

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

ORA-00972:标识符太长.

ORA-00972: identifier too long.

现在，我相信我知道oracle具有30个字节的限制，但这是我要执行的查询.

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

   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'

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

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.

truck ='104490'只是用于测试目的的样本整数.我尝试了各种解决方案，但是正确的方法(或寻找正确的方法)似乎使我难以理解.

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.

真诚的，我.

P.S.抱歉，这是一个愚蠢的问题.

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

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"现在是一个无效的标识符，我将在稍后返回.我认为我以错误的方式给它起了别名，但我不确定.可以找到答案.

"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.

  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'

我现在要使用这种语法，因为它比以前的语法更干净，更易于理解.但是我仍然遇到ORA-00904:"rideNumber":无效的标识符(这在连接行中也可能会计入shippingNumber.试图解决这一问题，谷歌返回了命名提示:没有成功.仍在搜索.

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.

Edit3:

    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:

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

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

对不起，我没有设计此数据库>):

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

Edit4/解决方案?

Edit4/solution?

这似乎行得通，奇怪的是.

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';

推荐答案

引号错误. "cef_tsroma.tsrido"应为"cef_tsroma"."tsrido" ...

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

edit2:在WHERE或JOIN子句中，新名称(别名)无效.例如，表DUAL的列X可以重命名，但是必须在WHERE子句中使用旧名称进行寻址:

On edit2: The new names (aliases) are not affective in the WHERE or JOIN clause. To use a example, the column X of the table DUAL can be renamed, but must be addressed with the old name in the WHERE clause:

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

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

在edit3上:您的表名和列名看起来像普通的Oracle名称，不区分大小写.因此，您可以删除所有双引号:

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

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';

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

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坚持完全使用这种拼写.

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

因此，在您的情况下，表/视图"cef_tsroma"不存在，但是cef_tsroma或CEF_TSROMA或"CEF_TSROMA"确实存在...

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

这篇关于ORA-00972:标识符太长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！