ORA-00972: Identifier is too long

1
votes

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

ORA-00972: identifier too long.

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'

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

Sincerely, me.

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

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.

EDIT2: 

  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.

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:

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

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

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';
3
sqloracleoracle11galias
You have to use the original name, not the aliased name in the JOIN clause: ... ON "cef_tsroma"."dosvlg" = "cef_v_cv_shipment"."shipmentNumber" - wolφi
I have attempted this but it also does not work. ERROR ORA-00904: "cef_v_cv_shipment"."shipmentNumber": invalid identifier - Perfection
With the table aliases ct and cs the join syntax is ON ct.dosvlg = cs.shipmentNumber. - wolφi
See edit3, still returns an error. - Perfection
Oh dear! Can you query the correct spelling of the table and column names, please? SELECT table_name, column_name FROM user_tab_columns WHERE UPPER(table_name) IN ('CEF_TSROMA','CEF_V_CW_SHIPMENT'); - wolφi

3 Answers

2
votes

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

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'
2
votes

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

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

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

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.

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_tsroma or CEF_TSROMA or "CEF_TSROMA" does...

1
votes

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'