[WEBCLIENT-35] Exclusion flag is applied to the wrong panel Created: 15/Aug/11 Updated: 05/Jun/14 Resolved: 27/Oct/11 |
|
| Status: | Closed |
| Project: | i2b2 Web Client |
| Component/s: | CRC Cell |
| Affects Version/s: | 1.6.00-RC4 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major |
| Reporter: | ajay m | Assignee: | Mike Mendis |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| i2b2 Feature/s: |
Queries
|
| Participant/s: |
| Description |
|
I have the following search criteria in i2b2: (all males who are not in the age group of 10 - 17 and whose marital status is not 'married') Panel1: Age 10 - 17; excluded Panel2: Male Panel3:Marital status - selected all except Married. Query returns 0. It looks like the the exclusion is being applied to the third panel. Entry from the QT_QUERY_MSTR: " insert into i2b2demodata.QUERY_GLOBAL_TEMP ( patient_num , panel_count ) select distinct patient_num ,1 from i2b2demodata.patient_dimension pat <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count =-1 where not exists ( select patient_num ,1 from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Demographics\Marital Status\Common Law\%') group by patient_num having count(*) >= 1 ) t where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 1 and i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num ) <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count =-1 where not exists ( select patient_num ,1 from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Demographics\Marital Status\Divorced\%') group by patient_num having count(*) >= 1 ) t where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 1 and i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num ) <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count =-1 where not exists ( select patient_num ,1 from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Demographics\Marital Status\Other\%') group by patient_num having count(*) >= 1 ) t where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 1 and i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num ) <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count =-1 where not exists ( select patient_num ,1 from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Demographics\Marital Status\Partner\%') group by patient_num having count(*) >= 1 ) t where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 1 and i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num ) <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count =-1 where not exists ( select patient_num ,1 from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Demographics\Marital Status\Separated\%') group by patient_num having count(*) >= 1 ) t where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 1 and i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num ) <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count =-1 where not exists ( select patient_num ,1 from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Demographics\Marital Status\Single\%') group by patient_num having count(*) >= 1 ) t where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 1 and i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num ) <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count =-1 where not exists ( select patient_num ,1 from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Demographics\Marital Status\Unknown\%') group by patient_num having count(*) >= 1 ) t where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 1 and i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num ) <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count =-1 where not exists ( select patient_num ,1 from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Demographics\Marital Status\Widowed\%') group by patient_num having count(*) >= 1 ) t where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 1 and i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num ) <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count = 2 where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 1 <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count = -1 where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 2 and exists ( select 1 from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where patient_num IN (select patient_num from i2b2demodata.patient_dimension where birth_date BETWEEN sysdate - (365.25*18) AND sysdate - (365.25*10) ) group by patient_num having count(*) >= 1 ) t where i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num ) <*> update i2b2demodata.QUERY_GLOBAL_TEMP set panel_count =3 where exists ( select 1 from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2demodata.observation_fact where concept_cd IN (select concept_cd from i2b2demodata.concept_dimension where concept_path LIKE '\i2b2\Demographics\Gender\Male\%') group by patient_num having count(*) >= 1 ) t where i2b2demodata.QUERY_GLOBAL_TEMP.panel_count = 2 and i2b2demodata.QUERY_GLOBAL_TEMP.patient_num = t.patient_num ) <*> insert into i2b2demodata.DX ( patient_num ) select * from ( select distinct patient_num from i2b2demodata.QUERY_GLOBAL_TEMP where panel_count = 3 ) q" |
| Comments |
| Comment by Mike Mendis [ 27/Oct/11 ] |
|
Just tried it, it works fine. try the following instead > Panel1: Age 10 - 17 > Panel2: Male > Panel3: Married - Inverted |