Uploaded image for project: 'i2b2 Core Software'
  1. i2b2 Core Software
  2. CORE-80

Date restrict on non obs_fact values not added to generated SQL



    • Bug
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 1.6.08
    • 1.7.02
    • CRC Cell
    • None
    • Rank:
    • Constraints - Date, Queries


      When building queries for "Inpatient" visit dimension term, generated SQL no longer contains the visit date restriction parameters if dates are restricted on the front end. For example, we query simply "Inpatient" for 01/01/13 - 01/31/13 and get the same result as when we query with no date restrict for the same term. In 1.5, the date restrict worked on non-observation_fact terms, but on 1.6 it doesn't seem to function appropriately. Tested in the Partners demo domain with the same result. See below for 1) example of 1.5 query with visit dim date restrict and 2) example of 1.6 query with date restrict on the front end of the web client and workbench, but not in the generated SQL. Was this function deprecated for some reason with the upgrade to 1.6? I cannot find any documentation on this particular issue.
      INSERT INTO i2b2_did_crc.QUERY_GLOBAL_TEMP ( patient_num, panel_count)
      SELECT patient_num, 1 FROM (
      SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num FROM i2b2_did_crc.visit_dimension WHERE (((encounter_num IN (select encounter_num from i2b2_did_crc.visit_dimension c where inout_path LIKE '\i2b2\Visit Details\Inpatient\%')) AND start_date>= to_date('01-Jan-2013 00:00:00','DD-MON-YYYY HH24:MI:SS') ))
      group by patient_num having count(*) >=1) t
      INSERT INTO i2b2_did_crc.DX (patient_num) SELECT * FROM (
      SELECT DISTINCT t.patient_num FROM i2b2_did_crc.QUERY_GLOBAL_TEMP t WHERE panel_count = 1
      ) q
      select count(distinct patient_num) as patient_num_count from (

      (select patient_num ,1 as panel_count from ( select /*+ index(observation_fact fact_cnpt_pat_enct_idx) */ patient_num from i2b2_did_crc.visit_dimension where encounter_num IN (select encounter_num from i2b2_did_crc.visit_dimension where inout_path LIKE '\i2b2\Visit Details\Inpatient\%' {ESCAPE '?'} ) group by patient_num ) t

       ) allitem




            mem61 Mike Mendis
            napathy Nate Apathy
            0 Vote for this issue
            0 Start watching this issue