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"