non estrae scompenso

nanci giacinto Dom 29 Mar 2015 - 9:36

BUON GIORNO LA QUERY SOTTO RIPORTATA MI DA SEMPRE RISULTATO ZERO EPPURE GLI SCOMPENSATI REGISTRATI NELL'ARCHIVIO MW CON CODICE 428.9/00 CI SONO. HO PROVATO A SOSTITUIRE IL CODICE CON 428, 428%%, 428.9% ETC MA NIENTE. CHI MI SVELA L'ARCANO? 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 '428.9/00')
___SCOMPENSO_CARDIACO___,
(select max(a.cp_code) from cart_pazpbl a
where a.codice=p.codice and a.cp_code like '428.9/00' and a.data_open between today()-365 and today()) ___INCID_12mesi_SCOMPENSO_CARDIACO___,

(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()-365 and today()) ______ferquenza_ultimo_anno______,

(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 avg(a.ac_val) from cart_accert a
where a.codice=p.codice and a.ac_des like '%bmi%' and
a.data_open between today()-1095 and today()
and a.ac_val is not null group by a.codice) ______media_bmi_3anni______,
(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%bmi%'
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 '%bmi%' 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() ) ______bmi_val_ultimo_15mesi______,

(select avg(a.ac_val) from cart_accert a
where a.codice=p.codice and a.ac_des like '%circonferenza_vita%' and
a.data_open between today()-1095 and today()
and a.ac_val is not null group by a.codice) ______media_circonferenza_vita_3anni______,
(select a.ac_val from cart_accert a
where a.codice=p.codice and a.ac_des like '%circonferenza_vita%'
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 '%circonferenza_vita%' 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() ) ______circonferenza_vita_val_ultimo_15mesi______,

(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c01bc03'
and
a.data_open between today()-365 and today()) ____propafenone____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c07ab07'
and
a.data_open between today()-365 and today()) ____bisoprololo____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'C07ab03'
and
a.data_open between today()-365 and today()) ____atenololo____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c07ab02%'
and
a.data_open between today()-365 and today()) ____metoprololo____,
(select distinct 'si'from cart_terap a
where a.codice=p.codice and a.co_atc like 'c07ag02'
and
a.data_open between today()-365 and today()) ____carvedilolo____,
(select distinct 'si'from cart_terap a
where a.codice=p.codice and a.co_atc like 'c07ab04'
and
a.data_open between today()-365 and today()) ____acebutololo____,
(select distinct 'si'from cart_terap a
where a.codice=p.codice and a.co_atc like 'c07ab05'
and
a.data_open between today()-365 and today()) ____betaxololo____,
(select distinct 'si'from cart_terap a
where a.codice=p.codice and a.co_atc like 'c07ab08'
and
a.data_open between today()-365 and today()) ____celibrololo____,
(select distinct 'si'from cart_terap a
where a.codice=p.codice and a.co_atc like 'c07ab12'
and
a.data_open between today()-365 and today()) ____nebivololo____,
(select distinct 'si' from cart_terap a
where a.codice=p.codice and a.co_atc like 'c01eb17%'
and
a.data_open between today()-365 and today()) ____ivrabadina____
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 '428.9/00’)
and n.pa_drevoca is null
and p.decesso is null
and p.pa_convenzione not like 'l'

order by p.cognome, p.nome

Lucio Mignone Lun 30 Mar 2015 - 11:40

nanci giacinto ha scritto:BUON GIORNO LA QUERY SOTTO RIPORTATA MI DA SEMPRE RISULTATO ZERO EPPURE GLI SCOMPENSATI REGISTRATI NELL'ARCHIVIO MW CON CODICE 428.9/00 CI SONO. HO PROVATO A SOSTITUIRE IL CODICE CON 428, 428%%, 428.9% ETC MA NIENTE. CHI MI SVELA L'ARCANO? GRAZIE.

Forse hai installata la nuova versione di Millewin (di solito installata a chi lavora in rete locale) con datatbase POSTGRES?
Le altre vecchie query funzionano regolarmente?

nanci giacinto Lun 30 Mar 2015 - 16:58

Sì le vecchie query funzionano. Il data base è SYSBASE non postgres. Ho fatto una query sql non con i codici ICD9 ma con la dicitura "SCOMPENSO CARDIACO" e li estrae regolarmente.Grazie

**Admin** Lun 30 Mar 2015 - 21:37

Prova così:

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

(select distinct 'si' from cart_pazpbl a where a.codice=p.codice and c.cp_code like '428.%')
___SCOMPENSO_CARDIACO___,

(select distinct 'si' from cart_pazpbl a where a.codice=p.codice and a.cp_code like '428.%' and a.data_open between today()-365 and today()) ___INCID_12mesi_SCOMPENSO_CARDIACO___,

(select a.frequenza from cart_press a where a.codice=p.codice And a.data_open > Today()-365
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)))) ______ferquenza_ultimo_anno______,

(select Cast(avg(a.p_max) as Dec(5,2)) 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 Cast(avg(a.p_min) as Dec(5,2)) 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 Cast(avg(a.ac_val) as Dec(5,2)) from cart_accert a where a.codice=p.codice and a.ac_des like '%bmi%' and a.data_open between today()-1095 and today() and a.ac_val is not null group by a.codice) ______media_bmi_3anni______,

(select a.ac_val from cart_accert a where a.codice=p.codice and a.ac_des like '%bmi%'
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 '%bmi%' 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() ) ______bmi_val_ultimo_15mesi______,

(select Cast(avg(a.ac_val) as Dec(5,2)) from cart_accert a where a.codice=p.codice and a.ac_des like '%circonferenza_vita%' and a.data_open between today()-1095 and today() and a.ac_val is not null group by a.codice) ______media_circonferenza_vita_3anni______,

(select a.ac_val from cart_accert a where a.codice=p.codice and a.ac_des like '%circonferenza_vita%' 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 '%circonferenza_vita%' 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() ) ______circonferenza_vita_val_ultimo_15mesi______,

(select distinct 'si' from cart_terap a where a.codice=p.codice and a.co_atc like 'c01bc03' and
a.data_open between today()-365 and today()) ____propafenone____,
(select distinct 'si' from cart_terap a where a.codice=p.codice and a.co_atc like 'c07ab07'
and a.data_open between today()-365 and today()) ____bisoprololo____,
(select distinct 'si' from cart_terap a where a.codice=p.codice and a.co_atc like 'C07ab03'
and a.data_open between today()-365 and today()) ____atenololo____,
(select distinct 'si' from cart_terap a where a.codice=p.codice and a.co_atc like 'c07ab02%'
and a.data_open between today()-365 and today()) ____metoprololo____,
(select distinct 'si'from cart_terap a where a.codice=p.codice and a.co_atc like 'c07ag02'
and a.data_open between today()-365 and today()) ____carvedilolo____,
(select distinct 'si'from cart_terap a where a.codice=p.codice and a.co_atc like 'c07ab04'
and a.data_open between today()-365 and today()) ____acebutololo____,
(select distinct 'si'from cart_terap a where a.codice=p.codice and a.co_atc like 'c07ab05'
and a.data_open between today()-365 and today()) ____betaxololo____,
(select distinct 'si'from cart_terap a where a.codice=p.codice and a.co_atc like 'c07ab08'
and a.data_open between today()-365 and today()) ____celibrololo____,
(select distinct 'si'from cart_terap a where a.codice=p.codice and a.co_atc like 'c07ab12'
and a.data_open between today()-365 and today()) ____nebivololo____,
(select distinct 'si' from cart_terap a where a.codice=p.codice and a.co_atc like 'c01eb17%'
and a.data_open between today()-365 and today()) ____ivrabadina____

from pazienti p , nos_002 n, cart_pazpbl c, v_pazienti v, cart_pazpbl a
where p.codice=n.codice
and c.codice=n.codice
and v.codice=p.codice
and p.codice=a.codice
and c.cp_code like '428.%'
and (n.pa_drevoca is null Or n.Pa_Drevoca > Today())
and p.decesso is null
and p.pa_convenzione not like 'l'
order by p.cognome, p.nome

Cervino Mar 31 Mar 2015 - 1:00

prova a sostituire nella clausola finale : and (c.cp_code like '428.9/00’)

con :   And ( c.cp_code Like '428.9%' And cp_cod2 Like '00' ) )

               alcune possibili opzioni per la parte finale della query :

FROM (( pazienti p Left Outer Join nos_002 n ON p.codice = n.codice )
                                    Left Outer Join v_pazienti v ON   v.codice = p.codice )
                                    Left Outer Join   cart_pazpbl c ON c.codice = p.codice
Where n.pa_drevoca is null And p.decesso is null And p.pa_convenzione like 'S'
And ( c.cp_code Like '428.9%' And cp_cod2 Like '00' )
Order By p.cognome , p.nome

                         oppure

FROM ( pazienti p Left Outer Join nos_002 n ON p.codice = n.codice )
                                   Left Outer Join v_pazienti v ON   v.codice = p.codice
Where n.pa_drevoca is null And p.decesso is null And p.pa_convenzione like 'S'
And p.codice IN ( Select c.codice From cart_pazpbl c Where c.codice=p.codice
And ( c.cp_code Like '428.9%' And cp_cod2 Like '00' ) )
Order By p.cognome , p.nome

sarebbe meglio usare lo stesso alias per ogni singola tabella sia nella query principale che nelle subquery :

(Select Max( c.cp_code) From cart_pazpbl c Where c.codice=p.codice And ( c.cp_code Like '428.9%' And cp_cod2 Like '00' ) )     ___SCOMPENSO_CARDIACO___,

(Select Max( c.cp_code) From cart_pazpbl c Where c.codice=p.codice And ( c.cp_code Like '428.9%' And cp_cod2 Like '00' ) And c.data_open Between Today()-365 And Today() )   ___INCID_12mesi_SCOMPENSO_CARDIACO___,

Ti saluto , Sergio

nanci giacinto Ven 3 Apr 2015 - 19:54

BUONA SERA DOPO TANTI TENTATIVI HA FUNZIONATO DOPO AVER SOPPRESSO LA SUBQUERY DELLA PRESSIONE ARTERIOSA A SEI MESI. E' UN CASO OPPURE C'E' UN RAZIONALE? GRAZIE PER L'AIUTO CHE MI AVETE GIA' DATO.

**Admin** Ven 3 Apr 2015 - 20:29

nanci giacinto ha scritto:BUONA SERA DOPO TANTI TENTATIVI HA FUNZIONATO DOPO AVER SOPPRESSO LA SUBQUERY DELLA PRESSIONE ARTERIOSA A SEI MESI. E' UN CASO OPPURE C'E' UN RAZIONALE? GRAZIE PER L'AIUTO CHE MI AVETE GIA' DATO.

Prova a sostituire le subquery con:

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

nanci giacinto Sab 4 Apr 2015 - 20:28

BUONA SERA CON QUESTA CORREZIONE FUNZIONA. HO VISTO CHE NELLA SUBQUERY ERRATA C'ERA UNA PARENTESI ")" IN PIU' DOPO "is not null". GRAZIE

**Admin** Sab 4 Apr 2015 - 20:37

nanci giacinto ha scritto:BUONA SERA CON QUESTA CORREZIONE FUNZIONA. HO VISTO CHE NELLA SUBQUERY ERRATA C'ERA UNA PARENTESI ")" IN PIU' DOPO "is not null". GRAZIE

Infatti, le parentesi nel linguaggio SQL hanno la stessa funzione che in algebra.

Contenuto sponsorizzato

non estrae scompenso