Pass through Query Issue ORa-00972

[Pyromantic]

Heya!

I'm trying to run a pass through through on a oracle DB, but I get a "ORA-00972 Identifier too long", below is my code:

SELECT COMPANY, BRANCH, TRANSACTION_ID, WEEKENDING_DATE, TRANSACTION_TYPE, TRANSACTION_CODE, BILL_UNIT_COST, BILL_VALUE, BILL_VAT, BILL_CURRENCY, PAY_UNIT_COST, PAY_VALUE, PAY_VAT, PAY_CURRENCY, TRANSACTION_UNITS, PAY_STATUS, PAY_SESSION, PAYSLIP_NUMBER, PAYSLIP_DATE, PAY_PERIOD, SUN_AP_SESSION, BILL_STATUS, BILL_SESSION, INVOICE_NUMBER, INVOICE_DATE, BILL_PERIOD, SUN_AR_SESSION, CLIENT_ID, CLIENT, CONTRACT_ID, NO_INVOICE, NO_PAYSLIP, CLIENT_TS_NO, TS_SIGNED_BY, TS_SIGNED_ON, COMPASS_TRANSFER, WORKER_ID, WORKER, WORKER_COMPANY, WORKER_VAT, WORKER_STATUS, AGENT, QC_REFERENCE, EE_NUMBER, START_DATE, END_DATE, MARGIN_ADJUSTMENT, INVOICE_GROUP, PURCHASE_ORDER, CREATED, PAY_BRANCH, BILL_BRANCH, REQUIREMENT_NO, USER_FIELD1, USER_FIELD2, USER_FIELD3, USER_FIELD4, USER_FIELD5, USER_FIELD6, USER_FIELD7, USER_FIELD8, USER_FIELD9, USER_FIELD10, HELD_REASON, CONTACT_NAME, BILL_ADDRESS1, BILL_ADDRESS2, BILL_ADDRESS3, BILL_ADDRESS4, BILL_ADDRESS5, BILL_ADDRESS_PC
FROM EDWARDSG_VIEW_CONTRACT_TRANSACTIONS_DG
WHERE (((WEEKENDING_DATE)=#8/25/2007#));

Any ideas how this could be falling down! The odbc string is one I use all the time so it can't be that. Any help would be greatly appreciated. Thanks for reading!

 

[CT Witter]

Your table name is too long...

Oracle Product Document Library (C) Oracle Corporation
Manual: Database Error Reference: A90202-02

ORA-00972 identifier is too long

Cause: The name of a schema object exceeds 30 characters. Schema objects are tables, clusters, views, indexes, synonyms, tablespaces, and usernames.

Action: Shorten the name to 30 characters or less.

HTH,
CT

 

[Pyromantic]

Thanks for replying!

I had this horrible feeling it might be that, but err, I can't shorten the table name, that's the way the peice of software has been built and I cannot change the name of the table itself in the Oracle DB. Does that mean I'm kinda stuck ?

 

[CT Witter]

I would check with your dba.

Also, try changing your date delimiter...
The date delimiter is "#" in Access and " ' " (single quote) in Oracle.

Generally, you need to use "to_date('01/23/1978', 'mm/dd/yyyy')" to format a date for comparison in Oracle SQL whereas in Jet Sql you could simply use "#01/23/1978#".


Thanks,
CT

 

[Pyromantic]

lol, god help me then. Thanks for the help CT. Yeah, I already tried getting rid of the WHERE statement but I get the same error.

cheers!

 

[giacomo]

If the table already exists in your Oracle database then obviously the name is not too long... my hunch is that the schema name is EDWARDSG and the view name is VIEW_CONTRACT_TRANSACTIONS_DG.

So in your query if you change the table name to EDWARDSG.VIEW_CONTRACT_TRANSACTIONS_DG does that work for you?

hth,
Giacomo

 

[toilettenseife]

If the table already exists in your Oracle database then obviously the name is not too long... my hunch is that the schema name is EDWARDSG and the view name is VIEW_CONTRACT_TRANSACTIONS_DG.

So in your query if you change the table name to EDWARDSG.VIEW_CONTRACT_TRANSACTIONS_DG does that work for you?

hth,
Giacomo

Thank you for this post. I've been looking all over for this solution.