asked on

ORA-00972: identifier is too long

In trying to combine two queries, utilizing Toad Data Point Virtual Views with this SQL, I get this message: Not sure why?

Please note that my two queries, "ADT" and "PX" already include my where statements.

I just want to combine the data from these two queries to export into one report.

SELECT ADT.HAR
      , ADT."PAT NAME"
      , ADT.ATTENDING_PROV_ID
      , ADT.DISCH_DATE_TIME
      , ADT.PRIM_SVC_HA_C
      , ADT.NAME
      , ADT.INP_ADM_DATE
      , ADT.DISCH_DISP_C
      , ADT.PAT_MRN_ID
      , ADT.DRG_MPI_CODE
      , ADT.DRG_REIMBURSEMENT
      , ADT.HOSP_ADMSN_TYPE_C
      , ADT.ABSTRACT_USER_ID
      , ADT.DX_NAME
      , ADT.REF_BILL_CODE
      , ADT.NAME1
      , PX.PROC_DATE
      , PX.REF_BILL_CODE
      , PX.PROCEDURE_NAME
FROM
      {{ADT}} ADT
      LEFT OUTER JOIN
      {{PX}} PX
      ON (ADT.HAR = PX.HSP_ACCOUNT_ID)

Any ideas are greatly appreciated!

ASKER CERTIFIED SOLUTION

MikeOM_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SheriReyna

ASKER

Can you clarify the "generated queries containing too many characters? Do you mean the table name (fields) that the views are extracted from contain too many characters, or that the query itself has too many?

MikeOM_DBA

One or more "identifiers" in those fields may be too long ( > 32 char).
Sometimes it's a missing space between two identifiers...

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MikeOM_DBA

Exactly what I said.

PortletPaul

not quite, a difference of 2, but we certainly agree on the source of the problem.
(and your tip on a missing space is a really good one, particularly relevant if it's dynamic sql)

I was really just adding the URL's to be honest as I thought they might help.

Gerwin Jansen

Do both the 'source' queries for {{ADT}} and {{PX}} run without problems?

SheriReyna

ASKER

Thanks all - Both source queries do run without problems. I did create a field alias though for the longer columns for display purposes and renaming fields for clarity sake. For example, HSP_ACCOUNT.HSP_ACCOUNT_NAME AS "PAT NAME" is the AS . . . . included in the character length too?

SOLUTION

Gerwin Jansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gerwin Jansen

Would you like to comment on how you solved the issue? Your last comment ends with a question?

SheriReyna

ASKER

I am currently working to change the VARCHAR on the columns to 34 rather than 30 or 32, and attempting to identify any "empty" spaces within the SQL and changing my aliases as you indicated above. I will keep you posted as to what works. Thanks for all of your help!