Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 870
  • Last Modified:

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!
0
SheriReyna
Asked:
SheriReyna
  • 3
  • 3
  • 3
  • +1
3 Solutions
 
MikeOM_DBACommented:
Either the generated {{ADT}} or {{PX}} contain to many characters.
Perhaps they should not already include the where statements.
:p
0
 
SheriReynaAuthor Commented:
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?
0
 
MikeOM_DBACommented:
One or more  "identifiers" in those fields may be too long ( > 32 char).
Sometimes it's a missing space between two identifiers...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
PortletPaulCommented:
i.e. the problem is hidden in the contents of  {{ADT}} or {{PX}}
something in one (or both?) of these has an "identifier" that is too long (> 30 chars)
(which might be an alias)

http://ora-00972.ora-code.com/

http://www.dba-oracle.com/sf_ora_00972_identifier_is_too_long.htm
0
 
MikeOM_DBACommented:
Exactly what I said.
0
 
PortletPaulCommented:
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.
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Do both the 'source' queries for {{ADT}} and {{PX}} run without problems?
0
 
SheriReynaAuthor Commented:
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?
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Don't think so, ORA-00972 can be caused by column, alias, or table names which are too long. Can you try renaming your "PAT NAME" alias to PAT_NAME instead?
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Would you like to comment on how you solved the issue? Your last comment ends with a question?
0
 
SheriReynaAuthor Commented:
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!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now