• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1448
  • Last Modified:

SQL syntax problem (offiste) / ORA-00972: identifier is too long

Dear experts,

I currently have no access to my Oracle db (offsite)...but I need the attached .sql to work.
==> Currently it returns a "ORA-00972: identifier is too long" error...which I can not fix from my "notepad"

Can you help me to solve this SQL syntax problem (or explain why a 14557 cars long SQL is "too long") ?

Best regards
Telephone-KOdeSyntaxe.sql
0
BigSchmuh
Asked:
BigSchmuh
2 Solutions
 
madvedsCommented:
Anyway use more compacts way:
translate(numtel, '0' || translate(numtel, '_1234567890', '_'), '0')

instead of with chr(N)


btw WHERE clause has restriction and it depends from version and other reasons... in my 10g version your query works.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the error does not mean the SQL is too long, but some identifier.
usually, it's about some quotes not being closed....

however, I cannot see the actual problem in the sql posted
0
 
BigSchmuhAuthor Commented:
@madveds: will your
   translate(numtel, '0' || translate(numtel, '_1234567890', '_'), '0')
remove all but numeric cars from numtel col ?

@angelIII: I know what "identifier too long" means...but I can find it as well...
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
slightwv (䄆 Netminder) Commented:
You can also use REGEXP_REPLACE to strip out non-numerics:

select regexp_replace('1D3$f#rf3','[^[:digit:]]*','') from dual
/
0
 
madvedsCommented:
BigSchmuh: yes it is. it's exactly remove all non-numeric characters
0
 
BigSchmuhAuthor Commented:
The below SQL did work well to transform my NUMTEL
SELECT SUBSTR(NUMTEL,1,20), VALIDITE
       , SUBSTR(
                  CASE WHEN SUBSTR(LTRIM(NUMTEL),1,1)='+' THEN '+' ELSE '' END
                  ||TRANSLATE( NUMTEL, '0123456789'||TRANSLATE( NUMTEL,'_0123456789','_'), '0123456789')
                  ||LTRIM(' '||TRANSLATE( NUMTEL, '_+0123456789.-       ','_'))
                  ,1,20) FROM g_telephone t
WHERE (VALIDITE<>'N' or VALIDITE is NULL)
 AND SUBSTR(TYPETEL,1,2)<>'EM'
 AND (NUMTEL is NULL
   or Length(NUMTEL)<10
   or Length(TRANSLATE( NUMTEL, '0123456789'||TRANSLATE( NUMTEL,'_0123456789','_'), '0123456789'))<10
      or SubStr( TRANSLATE( NUMTEL, '0123456789'||TRANSLATE( NUMTEL,'_0123456789','_'), '0123456789')
                  ,1,1) in('1','2','3','4','5','6','7','8','9')
      or SubStr( TRANSLATE( NUMTEL, '0123456789'||TRANSLATE( NUMTEL,'_0123456789','_'), '0123456789')
                  ,1,2) in('00')
      ) AND ROWNUM <= 10000;

Removing the column name looks like solving the "ORA-00972: identifier is too long" problem...even if I don't understand why adding a column name raised this error.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now