vecchia query

Buona sera a tutti questa query funzionava benissimo in Sysbase adesso in Postgresql mi da "ERRORE SQLSTATE 42883 "LA FUNZIONE AVG(CHARACTER VARYING) NON ESISTE". E' POSSIBILE FARLA FUNZIONARE ANCHE IN POSTGRESQL? GRAZIE 



select distinct p.nome nome, p.cognome cognome, n.pa_uslcode cod_reg,
v.codmedico codice_medico,
days(p.nascita, today())/365 eta, p.sesso sesso,
today() data_odierna,
 
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '272%')
___dislipidemia____,
 
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '250%')
__diabete__,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '250%' and a.data_open between today()-365 and today())  ___INCID_12mesi_diab___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '414%')
___CARD_ISCH___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '414%' and a.data_open between today()-365 and today())  ___INCID_12mesi_CARD_ISCH___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and  a.cp_code like '413%' )
____angina_pect___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '413%' and a.data_open between today()-365 and today())  ___INCID_12mesi_angina___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and (a.cp_code like '410%%' or a.cp_code like '412%%'))
__infarto_mioc__,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and (a.cp_code like '410%'  or a.cp_code like '412%') and a.data_open between today()-365 and today())  ___INCID_12mesi_infarto___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '436.%')
___ICTUS__,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '436.%' and a.data_open between today()-365 and today())  ___INCID_12mesi_ICTUS___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '411%')
__SINDR_cORON__,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '411%' and a.data_open between today()-365 and today())  ___INCID_12mesi_SINDR_cORON___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '431%' or a.cp_code like '430%')
__emorragia_cerebrale___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '436%' and a.data_open between today()-365 and today())  ___INCID_12mesi_stroke___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like 'v45.82%')
__angioplastica_coronarica___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like 'v45.82%' and a.data_open between today()-365 and today())  ___INCID_12mesi_angioplastica_coronarica___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and (a.cp_code like '414.5%%' or a.cp_code like 'V45.81%'))
__bypass_aortocoronarico___,
 
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and (a.cp_code like '414.5%'  or a.cp_code like 'V45.81%') and a.data_open
between today()-365 and today())  ___INCID_12mesi_bypass_aortocoronarico___,
 
 
(select a.ac_val from cart_accert a
where a.codice=p.codice
and (a.ac_des like '%creatinina%' and a.ac_des not like '%clearance%creatinina%') and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and (b.ac_des like '%creatinina%'
and b.ac_des not like '%clearance%creatinina%') and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today() ) ______creatinina_ultima15mesi______,
 
 
 
 
 
(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%colesterolo%totale%'
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%colesterolo%totale%' and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today()) ______colest_tot_ultimo15mesi______,
 
 
 
(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%trigliceridi%'
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%trigliceridi%' and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today()) ______trigliceridi_ultimo15mesi______,
 
(select count(a.ac_val) from cart_accert a
where a.codice=p.codice and a.ac_des
like '%HDL%' and
a.data_open between today()-1095 and today()
and a.ac_val is not null group by a.codice) ______N_HDL_3anni______,
 
(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%HDL%'
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%HDL%' and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today())  ______HDL_ultimo15mesi______,
 
(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%LDL%'
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%LDL%' and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today())  LDL_ultimo15mesi,
 
(select avg(a.ac_val) from cart_accert a
where a.codice=p.codice
and (a.ac_des like '%glicemia%' and a.ac_des not like '%glicemia%post%prandiale%'
and a.ac_des not like '%ultima%misurazione%glicemia%') and
a.data_open between today()-1095 and today()
and a.ac_val is not null group by a.codice) ______media_glicemia_3anni______,
 
(select a.ac_val from cart_accert a
where a.codice=p.codice
and (a.ac_des like '%glicemia%' and a.ac_des not like '%glicemia%post%prandiale%'  
and a.ac_des not like '%ultima%misurazione%glicemia%') and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and (b.ac_des like '%glicemia%' and b.ac_des not like '%glicemia%post%prandiale%' and a.ac_des not like '%ultima%misurazione%glicemia%') and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today() ) ______glicemia_ultima15mesi______,
 
(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%omocisteina%'
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%omocisteina%' and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today())  ______omocist_ultimi15mesi______,
 
(select avg(a.p_max) from cart_press a where a.codice=p.codice and
a.data_open between today()-455 and today()
and a.p_max is not null group by a.codice) ______media_p_max_15mesi______,
(select avg(a.p_min) from cart_press a where a.codice=p.codice and
a.data_open between today()-455 and today()
and a.p_min is not null group by a.codice) ______media_p_min_15mesi______,
(select a.p_max from cart_press a
where a.codice=p.codice
and not exists (select b.codice from cart_press b where a.codice=b.codice and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and a.p_max is not null)
and a.data_open between today()-180 and today())  ______p_max_ultima_6mesi______,
(select a.p_min from cart_press a
where a.codice=p.codice
and not exists (select b.codice from cart_press b where a.codice=b.codice and 
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and a.p_min is not null)
and a.data_open between today()-180 and today())  ______p_min_ultima_6mesi______,
 
(select a.frequenza from cart_press a
where a.codice=p.codice
and not exists (select b.codice from cart_press b where a.codice=b.codice and 
(b.data_open>a.data_open or b.data_open=a.data_open and b.rowid>a.rowid))
and a.data_open between today()-180 and today())  ______ferquenza_ultimi_6mesi______,
 
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c04%'
and
a.data_open between today()-365 and today()) ____clopidrogel____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c06%'
and
a.data_open between today()-365 and today()) ____cardioaspirin____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'C05%'
and
a.data_open between today()-365 and today()) ____ticlopidina____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c22%'
and
a.data_open between today()-365 and today()) ____prasugel____,
(select distinct 'si'from cart_terap a
where a.codice=p.codice and a.co_atc like 'c24%'
and
a.data_open between today()-365 and today()) ____brilique____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c11%'
and
a.data_open between today()-365 and today()) ____ventavis____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c30%'
and
a.data_open between today()-365 and today()) ____duoplavin____
 
from pazienti p , nos_002 n, cart_pazpbl c, v_pazienti v
where p.codice=n.codice
and c.codice=n.codice
and v.codice=p.codice
and (c.cp_code like '401%' or c.cp_code like '405%')
and n.pa_drevoca is null
and p.decesso is null
and p.pa_convenzione not like 'l'
 
 
 

order by p.cognome, p.nome

buon giorno ho sostituito la subquuery in questo modo e mi da errore. la colonna '999' non esiste. dove ho sbagliato? grazie

select distinct p.nome nome, p.cognome cognome, n.pa_uslcode cod_reg,
v.codmedico codice_medico,
days(p.nascita, today())/365 eta, p.sesso sesso,
today() data_odierna,

(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '272%')
___dislipidemia____,

(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '250%')
__diabete__,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '250%' and a.data_open between today()-365 and today()) ___INCID_12mesi_diab___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '414%')
___CARD_ISCH___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '414%' and a.data_open between today()-365 and today()) ___INCID_12mesi_CARD_ISCH___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '413%' )
____angina_pect___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '413%' and a.data_open between today()-365 and today()) ___INCID_12mesi_angina___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and (a.cp_code like '410%%' or a.cp_code like '412%%'))
__infarto_mioc__,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and (a.cp_code like '410%' or a.cp_code like '412%') and a.data_open between today()-365 and today()) ___INCID_12mesi_infarto___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '436.%')
___ICTUS__,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '436.%' and a.data_open between today()-365 and today()) ___INCID_12mesi_ICTUS___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '411%')
__SINDR_cORON__,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '411%' and a.data_open between today()-365 and today()) ___INCID_12mesi_SINDR_cORON___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '431%' or a.cp_code like '430%')
__emorragia_cerebrale___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '436%' and a.data_open between today()-365 and today()) ___INCID_12mesi_stroke___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like 'v45.82%')
__angioplastica_coronarica___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like 'v45.82%' and a.data_open between today()-365 and today()) ___INCID_12mesi_angioplastica_coronarica___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and (a.cp_code like '414.5%%' or a.cp_code like 'V45.81%'))
__bypass_aortocoronarico___,

(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and (a.cp_code like '414.5%' or a.cp_code like 'V45.81%') and a.data_open
between today()-365 and today()) ___INCID_12mesi_bypass_aortocoronarico___,


(select a.ac_val from cart_accert a
where a.codice=p.codice
and (a.ac_des like '%creatinina%' and a.ac_des not like '%clearance%creatinina%') and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and (b.ac_des like '%creatinina%'
and b.ac_des not like '%clearance%creatinina%') and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today() ) ______creatinina_ultima15mesi______,





(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%colesterolo%totale%'
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%colesterolo%totale%' and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today()) ______colest_tot_ultimo15mesi______,



(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%trigliceridi%'
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%trigliceridi%' and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today()) ______trigliceridi_ultimo15mesi______,

(select count(a.ac_val) from cart_accert a
where a.codice=p.codice and a.ac_des
like '%HDL%' and
a.data_open between today()-1095 and today()
and a.ac_val is not null group by a.codice) ______N_HDL_3anni______,

(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%HDL%'
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%HDL%' and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today()) ______HDL_ultimo15mesi______,

(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%LDL%'
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%LDL%' and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today()) LDL_ultimo15mesi,


(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%omocisteina%'
and (a.ac_val is not null)
and not exists (select b.codice from cart_accert b where a.codice=b.codice and b.ac_des like '%omocisteina%' and
(b.data_open>a.data_open or (b.data_open=a.data_open and b.rowid>a.rowid))
and b.ac_val is not null)
and a.data_open between today()-455 and today()) ______omocist_ultimi15mesi______,
(Select Cast(Round(Avg( To_number( e.p_max , ‘999’ )))
As smallInt ) from cart_press e Where e.codice=p.codice And e.data-open Between Today()-455 and Today() And Ascii( e.p_min) Between 48 And 57 Group By e.codice )
_________________media_p_max_15mesi_____________,
(Select Cast(Round(Avg( To_number( e.p_min , ‘999’ )))
As smallInt ) from cart_press e Where e.codice=p.codice And e.data-open Between Today()-455 and Today() And Ascii( e.p_min) Between 48 And 57 Group By e.codice )
_________________media_p_man_15mesi_____________,

(select a.frequenza from cart_press a
where a.codice=p.codice
and not exists (select b.codice from cart_press b where a.codice=b.codice and
(b.data_open>a.data_open or b.data_open=a.data_open and b.rowid>a.rowid))
and a.data_open between today()-180 and today()) ______ferquenza_ultimi_6mesi______,

(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c04%'
and
a.data_open between today()-365 and today()) ____clopidrogel____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c06%'
and
a.data_open between today()-365 and today()) ____cardioaspirin____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'C05%'
and
a.data_open between today()-365 and today()) ____ticlopidina____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c22%'
and
a.data_open between today()-365 and today()) ____prasugel____,
(select distinct 'si'from cart_terap a
where a.codice=p.codice and a.co_atc like 'c24%'
and
a.data_open between today()-365 and today()) ____brilique____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c11%'
and
a.data_open between today()-365 and today()) ____ventavis____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c30%'
and
a.data_open between today()-365 and today()) ____duoplavin____

from pazienti p , nos_002 n, cart_pazpbl c, v_pazienti v
where p.codice=n.codice
and c.codice=n.codice
and v.codice=p.codice
and (c.cp_code like '401%' or c.cp_code like '405%')
and n.pa_drevoca is null
and p.decesso is null
and p.pa_convenzione not like 'l'



order by p.cognome, p.nome

buon pomeriggio. ho capito perchè la query corretta con le precise indicazioni di cervino non funziona. I motivi sono due: a) con copia e incolla i caratteri apici prima e dopo il numero 999 si sono modificati e 2) è scorretta la scrittura di a.data-open che doveva essere invece scritta con a.data_open. La query cosi' funziona perfettamente e non impiega motlo ad estrarre i dati.