Error ORA-00972

Toad for Oracle
1

Hi,

this is my query:

select
anno_elab as “anno”,
1 as “contatore”,
cartella.n_cartella as “cartella”,
substr(trim(cartella.cognome)||’ ‘||trim(cartella.nome),1,40) as “cognome_nome”,
cartella.data_nasc as “data_nascita”,
anno_elab - to_number(to_char(cartella.data_nasc,‘YYYY’),‘9999’) as “eta”,
cartella.sesso,
cartella.cod_com_nasc as “cod_comune_nascita”,
trim(comuni.descrizione) as “comune_nascita”,
cartella.cittadinanza as “cod_cittadinanza”,
trim(cittadin.des_cittadin) as “cittadinanza”,
substr(cittadin.flag_cittadin,1,1) as “ue_extraue”,
cartella.cod_reg as "codice_sanitario,
cartella.cod_fisc as “codice_fiscale”,
cartella.cod_usl as “codice_usl”,
cartella.data_apertura,
cartella.data_chiusura,
trim(tab_voci.tab_descrizione) as “motivo_chiusura”,
anagra_c.citta as “cod_comune_res”,
trim(comuni_res.descrizione) as “comune_res”,
anagra_c.indirizzo as “indirizzo_res”,
trim(areadis_res.descrizione) as “areadis_res”,
anagra_c.dom_citta as “cod_comune_dom”,
trim(comuni_dom.descrizione)as “comune_dom”,
anagra_c.dom_indiriz as “indirizzo_dom”,
trim(areadis_dom.descrizione) as “areadis_dom”,
trim(zone.descrizione_zona) as “zona”,
trim(distretti.des_distr) as “distretto”,
anagra_c.cod_med as “cod_mmg”,
substr(trim(medici.mecogn)||’ ‘||medici.menome,1,40) as “mmg”,
medici.mefisc as “cod_fisc_mmg”,
region.region as “regione”,
tabusl.desusl as “usl_residenza”,
ass_anagrafica.cod_operatore as “pi_cod_operatore”,
substr(trim(op_pi.cognome)||’ ‘||trim(op_pi.nome),1,40) as “pi_operatore_pi”,
ass_anagrafica.cod_presidio as “pi_cod_presidio”,
trim(presidi_pi.despres) as “pi_presidio”,
presidi_pi.coddistr as “pi_cod_distretto”,
trim(distretti_pi.des_distr) as “pi_distretto”,
trim(zone_pi.descrizione_zona) as “pi_zona”,
ass_anagrafica.progressivo as “pi_num_scheda”,
ass_anagrafica.data_reg as “pi_data_reg”,
puauvm.pr_data_richiesta as “pua_data_richiesta”,
puauvm.pr_data_avvio as “pua_data_avvio”,
puauvm.pr_data_puac as “pua_data_puac”,
puauvm.pr_protoc_domanda as “pua_protoc_domanda”,
puauvm.pr_mmg_data_richiesta as “pua_mmg_data_richiesta”,
puauvm.pr_mmg_data_sollecito as “pua_mmg_data_sollecito”,
puauvm.pr_mmg_risposta_neg as “pua_mmg_risposta_neg”,
puauvm.pr_scheda_1 as “pua_scheda_1”,
puauvm.pr_scheda_data_1 as “pua_scheda_data_1”,
puauvm.pr_soc_data_visita as “pua_soc_data_visita”,
puauvm.pr_soc_effettuata as “pua_soc_effettuata”,
puauvm.pr_soc_codice as “pua_soc_codice”,
substr(trim(op_soc.cognome)||’ ‘||trim(op_soc.nome),1,40) as “pua_soc_operatore”,
puauvm.pr_inf_data_visita as “pua_inf_data_visita”,
puauvm.pr_inf_effettuata as “pua_inf_effettuata”,
puauvm.pr_inf_codice as “pua_inf_codice”,
substr(trim(op_inf.cognome)||’ '||trim(op_inf.nome),1,40) as “pua_inf_operatore”,
puauvm.pr_stato_convoc as “pua_stato_convoc”,
puauvm.pr_data_seduta as “pua_data_seduta”,
puauvm.pr_data_verbale_uvm as “pua_data_verbale_uvm”,
puauvm.pr_num_verbale as “pua_num_verbale”,
puauvm.pr_sede as “pua_sede”,
trim(distretti_sede.des_distr) as “pua_distretto”,
puauvm.pr_centro_soc as “pua_centro_soc”,
trim(presidi_sede.despres) as “pua_presidio”,
trim(zone_sede.descrizione_zona) as “pua_zona”,
puauvm.pr_autosuff as “pua_autosuff”,
puauvm.pr_revisione as “pua_revisione”,
puauvm.pr_data_revisione as “pua_data_revisione”,
puauvm.pr_flag_urgente as “pua_flag_urgente”,
puauvm.pr_data_chiusura as “pua_data_chiusura”,
trim(tab_voci1.tab_descrizione) as “pua_motivo_chiusura”,
trim(tab_pap.descrizione) as “pua_pianoint”,
puauvm.pr_pianoint_ini as “pua_pianoint_ini”,
puauvm.pr_pianoint_fin as “pua_pianoint_fin”,
puauvm.pr_pianoint_spesa as “pua_pianoint_spesa”,
0 as “pua_tempo_t”,
0 as “pua_liv_isogravita”

from sinsnt_test.cartella,
sinsnt_test.comuni,
sinsnt_test.comuni comuni_res,
sinsnt_test.comuni comuni_dom,
sinsnt_test.cittadin,
sinsnt_test.tab_voci,
sinsnt_test.tab_voci tab_voci1,
sinsnt_test.anagra_c,
sinsnt_test.areadis areadis_res,
sinsnt_test.areadis areadis_dom,
sinsnt_test.medici,
sinsnt_test.region,
sinsnt_test.tabusl,
sinsnt_test.zone,
sinsnt_test.zone zone_pi,
sinsnt_test.zone zone_sede,
sinsnt_test.distretti,
sinsnt_test.distretti distretti_pi,
sinsnt_test.distretti distretti_sede,
sinsnt_test.operatori op_pi,
sinsnt_test.operatori op_soc,
sinsnt_test.operatori op_inf,
sinsnt_test.presidi presidi_sede,
sinsnt_test.presidi presidi_pi,
sinsnt_test.ass_anagrafica,
sinsnt_test.puauvm,
sinsnt_test.tab_pap,
sinsnt_test.anno_elab
where puauvm.pr_data_puac

and (puauvm.pr_data_chiusura
>= to_date(‘01-01-’||to_char(anno_elab), ‘DD-MM-YYYY’)
or puauvm.pr_data_chiusura is null)
and puauvm.n_cartella = cartella.n_cartella
and cartella.n_cartella = anagra_c.n_cartella
and anagra_c.data_variazione =
(select max(data_variazione) from sinsnt_test.anagra_c
where cartella.n_cartella = anagra_c.n_cartella)
and cod_com_nasc = comuni.codice
and cartella.cittadinanza = cittadin.cd_cittadin
and (‘MOTCHI’= tab_voci.tab_cod
and cartella.motivo_chiusura=tab_voci.tab_val)
and anagra_c.citta = comuni_res.codice
and anagra_c.dom_citta = comuni_dom.codice
and anagra_c.areadis = areadis_res.codice
and anagra_c.dom_areadis = areadis_dom.codice
and anagra_c.cod_med = medici.mecodi
and anagra_c.regione = region.cd_reg
and (anagra_c.regione||anagra_c.usl) = tabusl.cd_usl
and areadis_dom.cod_distretto = distretti.cod_distr
and distretti.cod_zona = zone.codice_zona
and puauvm.pr_sede = presidi_sede.codpres
and presidi_sede.coddistr = distretti_sede.cod_distr
and distretti_sede.cod_zona = zone_sede.codice_zona
and puauvm.pr_soc_codice = op_soc.codice
and puauvm.pr_inf_codice = op_inf.codice
and puauvm.pr_pianoint = tab_pap.codice
and (‘VALPCMCH’= tab_voci1.tab_cod
and puauvm.pr_motivo_chiusura=tab_voci1.tab_val)
and (puauvm.n_cartella = ass_anagrafica.n_cartella
and puauvm.pr_data = ass_anagrafica.pr_data
and puauvm.pr_progr = ass_anagrafica.pr_progr)
and ass_anagrafica.cod_operatore = op_pi.codice
and ass_anagrafica.cod_presidio = presidi_pi.codpres
and presidi_pi.coddistr = distretti_pi.cod_distr
and distretti_pi.cod_zona = zone_pi.codice_zona

why I’ve the error: ORA-00972: identifier is too long’

Thanks
Mariarita

2

Bringing this question into the Toad realm …

Copy and paste the query into the editor window and the highlighting reveals a missing double-quote (’"’) at the end of ‘cartella.cod_reg as "codice_sanitario’. Thanks Toad.

3

codice_sanitario is missing closing double-quote (“)

4

Thanks,

the same error there is in this query:

SELECT
anno_elab as “anno”,
1 as “numero”,
cartella.n_cartella as “cartella”,
SUBSTR(TRIM (cartella.cognome) || ’ ’ || TRIM (cartella.nome), 1,40) as “cognome_nome”,
cartella.data_nasc as “data_nascita”,
anno_elab - to_number (to_char (cartella.data_nasc, ‘yyyy’), ‘9999’) as “eta”,
cartella.sesso,
cartella.cod_com_nasc as “cod_comune_nascita”,
TRIM (comuni1.descrizione) as “comune_nascita”,
cartella.cittadinanza as “cod_cittadinanza”,
TRIM (cittadin.des_cittadin) as “cittadinanza”,
cartella.cod_reg as “codice_sanitario”,
cartella.cod_fisc as “codice_fiscale”,
cartella.cod_usl as “codice_usl”,
cartella.data_apertura,
cartella.data_chiusura,
DECODE (cartella.motivo_chiusura,‘1’, ‘Trasferimento’,‘2’, ‘Decesso’) as “motivo_chiusura”,
anagra_c.citta as “cod_comune_res”,
TRIM (comuni2.descrizione) as “comune_resid”,
TRIM (anagra_c.indirizzo) as “ind_resid”,
TRIM (areadis1.descrizione) as “areadist_resid”,
anagra_c.dom_citta as “cod_comune_domic”,
TRIM (comuni3.descrizione) as “comune_domic”,
TRIM (anagra_c.dom_indiriz) as “ind_domic”,
TRIM (areadis2.descrizione) as “areadist_domic”,
anagra_c.cod_med as “cod_mmg”,
SUBSTR (TRIM (medici.mecogn) || ’ ’ || TRIM (medici.menome), 1, 40) as “cognome_nome_mmg”,
medici.mefisc as “cod_fisc_mmg”,
region.region as “regione”,
tabusl.desusl as “usl_resid”,
skinf.n_contatto,
skinf.ski_data_apertura,
skinf.ski_data_uscita as “ski_data_chiusura”,
skinf.ski_les_dec,
skidecubito_t.skdt_data,
DECODE (skidecubito_t.skdt_dolore,
‘1’, ‘NO’,
‘2’, ‘SI,DA VALUTARE’,
‘3’, ‘SI,VALUTATO’
),
skidecubito_t.skdt_note,
skidecubito_t.skdt_trattamento,
skidecubito_d.skdd_prog,
DECODE (skidecubito_d.skdd_sede,
‘1’, ‘OCCIPITE’,
‘2’, ‘PADIGLIONE AURICOLARE DX’,
‘3’, ‘PADIGLIONE AURICOLARE SN’,
‘4’, ‘RACHIDE’,
‘5’, ‘SACRO-COCCIGEO’,
‘6’, ‘TRONCATERE DX’,
‘7’, ‘TRONCATERE SN’,
‘8’, ‘TALLONE DX’,
‘9’, ‘TALLONE SN’,
‘10’, ‘MALLEOLO INT. DX’,
‘11’, ‘MALLEOLO INT. SN’,
‘12’, ‘MALLEOLO EST. DX’,
‘13’, ‘MALLEOLO EST. SN’,
‘14’, ‘ALTRA SEDE’
),
DECODE (skidecubito_d.skdd_stadio,
‘0’, ‘GUARIGIONE’,
‘1’, ‘STADIO I’,
‘2’, ‘STADIO I-N’,
‘3’, ‘STADIO II’,
‘4’, ‘STADIO II-N’,
‘5’, ‘STADIO III’,
‘6’, ‘STADIO III-N’,
‘7’, ‘STADIO IV’,
‘8’, ‘STADIO IV-N’
),
DECODE (skidecubito_d.skdd_diametro,
‘1’, ‘MINORE DI 5’,
‘2’, ‘TRA 5 E 10’,
‘3’, ‘MAGGIORE DI 10’
),
skidecubito_d.skdd_data_val,
skidecubito_d.skdd_data_guarigione

FROM sinsnt_test.cartella,
sinsnt_test.anno_elab,
sinsnt_test.comuni comuni1,
sinsnt_test.comuni comuni2,
sinsnt_test.comuni comuni3,
sinsnt_test.cittadin,
sinsnt_test.anagra_c,
sinsnt_test.areadis areadis1,
sinsnt_test.areadis areadis2,
sinsnt_test.medici,
sinsnt_test.region,
sinsnt_test.tabusl,
sinsnt_test.skinf,
sinsnt_test.skidecubito_t,
sinsnt_test.skidecubito_d

WHERE skinf.n_cartella = cartella.n_cartella
AND skinf.n_cartella = anagra_c.n_cartella
AND anagra_c.data_variazione =
(SELECT MAX (data_variazione)
FROM sinsnt_test.anagra_c
WHERE cartella.n_cartella = anagra_c.n_cartella)
and skinf.n_contatto =
(select max(n_contatto) from sinsnt_test.skinf
where cartella.n_cartella = skinf.n_cartella
and ski_data_apertura

  and (ski_data_uscita
      >= to_date('01-01-'||to_char(anno_elab), 'DD-MM-YYYY')
                                  or ski_data_uscita is null))
  AND cartella.cod_com_nasc = comuni1.codice
  AND anagra_c.citta = comuni2.codice
  AND anagra_c.dom_citta = comuni3.codice
  AND anagra_c.areadis = areadis1.codice
  AND anagra_c.dom_areadis = areadis2.codice
  AND anagra_c.cod_med = medici.mecodi
  AND cartella.cittadinanza = cittadin.cd_cittadin
  AND anagra_c.regione = region.cd_reg
  AND anagra_c.usl = tabusl.cd_usl
  AND skinf.n_cartella = skidecubito_t.n_cartella
  AND skinf.n_cartella = skidecubito_d.n_cartella
  AND skinf.n_contatto = skidecubito_t.n_contatto
  AND skinf.n_contatto = skidecubito_d.n_contatto

but with other tools can not find the error.

:frowning:

5

Afternoon Mari,

$ oerr ora 972

00972, 00000, “identifier is too long”
// *Cause: An identifier with more than 30 characters was specified.
// *Action: Specify at most 30 characters.

Unfortunately, I don’t see where it could be in your SELECT statement. :frowning:

Cheers,
Norm. [TeamT]

Norman Dunbar
Contract Senior Oracle DBA
Capgemini Database Build Team
Internal : 7 28 2051
External : 0113 231 2051

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

6

Check your CASE statements, looks like they need a name/alias.

See Toad “snippets” for decode: DECODE( expr , value1 [ , return1 ,
value2 , return2 …, ] default )

So replace….

DECODE ( skidecubito_t . skdt_dolore ,

‘1’ , ‘NO’ ,

‘2’ , ‘SI,DA VALUTARE’ ,

‘3’ , ‘SI,VALUTATO’ ) ,

With….

DECODE ( skidecubito_t . skdt_dolore ,

‘1’ , ‘NO’ ,

‘2’ , ‘SI,DA VALUTARE’ ,

‘3’ , ‘SI,VALUTATO’ )

S_DOLARE ,

7

Check your CASE statements, looks like they need a name/alias.
Nice catch Deb!

Cheers,
Norm. [TeamT]

Norman Dunbar
Contract Senior Oracle DBA
Capgemini Database Build Team
Internal : 7 28 2051
External : 0113 231 2051

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

8

Thanks – comes with years of writing and reading bad SQL J …and Toad
formatting helps lots too.

9

On 20/10/11 15:28, Flad Deborah M. (DSCYF) wrote:

Thanks – comes with years of writing and reading bad SQL J..and Toad
formatting helps lots too.
Funny thing is, I tried a huge DECODE in Toad, and got an extremely wide
column header and a result. No error. :frowning:

--
Cheers,
Norm. [TeamT]

10

I don’t think the alias is actually required no matter how long the expression for the column. But if you do specify an alias, it needs to be 30 characters or less.

11

Thanks very much, I’ve solved with alias

12

I don't think the alias is actually required

Correct. But the longer you have your expression, the nicer an alias becomes
just to keep your column size down… speaking from sqlpplus of course :wink:

I haven’t actually reviewed the sql fully, but I think Deb got the primary
issue with the closing double quotes for that one column. The error message
would have arisen because the double quotes altered the parsing on the rest of
the query.

As a side observation: Its good practice to have defaults on the decode though
so the developer is in total control of what the value that emerges is.

Roger S.