Re: Error ORA-00972
mariarita <[email protected]>
| Newsgroups | gmane.comp.db.oracle.toad.free |
|---|---|
| Message-ID | <28044833.245071319123448718.JavaMail.SYSTEM@alvqcfw02> |
Message from: mari 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
__
Author: Deborah Flad
Date: Thu Oct 20 06:58:31 PDT 2011
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 ,
__
Author: Norman Dunbar
Date: Thu Oct 20 06:29:36 PDT 2011
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. :-(
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
__
Author: mariarita
Date: Thu Oct 20 06:29:36 PDT 2011
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
= 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.
:-(
__
Author: Deborah Flad
Date: Thu Oct 20 05:22:32 PDT 2011
codice_sanitario is missing closing double-quote (“)
__
Author: Brian Ray
Date: Thu Oct 20 04:39:21 PDT 2011
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.
__
Author: mariarita
Date: Thu Oct 20 03:52:16 PDT 2011
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
= 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
__
_______________________________________