FORUM PER UTENTI MILLEWIN
Attenzione !
Per intervenire sul forum è necessario essere registrati e connessi.

L'Amministratore

****************************************************
“Se tu hai una mela ed io ho una mela, e ce la scambiamo, alla fine tu ed io avremo sempre una mela ciascuno.
Ma se tu hai un’idea ed io ho un’idea, e ce la scambiamo, allora avremo entrambi due idee”.

George Bernard Shaw
****************************************************

vecchia query

Andare in basso

vecchia query

Messaggio  nanci giacinto il Mar 19 Set 2017 - 23:25

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
avatar
nanci giacinto
Membro Junior
Membro Junior

Messaggi : 126
Punti : 2857
Voti per importanza dei messaggi : -1
Data d'iscrizione : 02.03.11

Visualizza il profilo dell'utente

Torna in alto Andare in basso

Re: vecchia query

Messaggio  Cervino il Mer 20 Set 2017 - 0:43

La query si blocca in quanto i valori di PA  e di alcuni accertamenti sono sorprendentemente salvati in formato Character Varying e non come Values ( numeri : Integer , SmallInt ... ) e quindi bisogna prima di calcolare il valore medio, eseguire una trasformazione , ad esempio : 

(Select  Cast( Round( Avg( To_Number( a.ac_val , '999' ))) As SmallInt )  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  Ascii( a.ac_val ) Between 48 And 57 group by a.codice) ______media_glicemia_3anni______,


(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_min_15mesi______,


Basta sostituire le subquery con la funzione AVG come nell' esempio sopra indicato .
in ogni caso la query rimane pesante ( di vetusta concezione ed impostazione ) x il motore di ricerca : 20 secondi x ottenere  ~ 350 righe sono un eternità ( di solito max 5 - 7 sec x ricerche di tal genere ) ed assai imprecisa ( ovviamente x il catalogo Accertamenti della regione Lombardia )

Ti saluto ,  Sergio

Cervino
Membro Junior
Membro Junior

Messaggi : 219
Punti : 2901
Voti per importanza dei messaggi : 16
Data d'iscrizione : 03.03.11
Età : 63
Località : Orzivecchi (BS)

Visualizza il profilo dell'utente

Torna in alto Andare in basso

vecchia query

Messaggio  nanci giacinto il Gio 21 Set 2017 - 12:11

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

avatar
nanci giacinto
Membro Junior
Membro Junior

Messaggi : 126
Punti : 2857
Voti per importanza dei messaggi : -1
Data d'iscrizione : 02.03.11

Visualizza il profilo dell'utente

Torna in alto Andare in basso

vecchia query

Messaggio  nanci giacinto il Mer 27 Set 2017 - 18:34

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.
avatar
nanci giacinto
Membro Junior
Membro Junior

Messaggi : 126
Punti : 2857
Voti per importanza dei messaggi : -1
Data d'iscrizione : 02.03.11

Visualizza il profilo dell'utente

Torna in alto Andare in basso

Re: vecchia query

Messaggio  Contenuto sponsorizzato


Contenuto sponsorizzato


Torna in alto Andare in basso

Torna in alto

- Argomenti simili

 
Permessi di questa sezione del forum:
Non puoi rispondere agli argomenti in questo forum