[CORE-80] Date restrict on non obs_fact values not added to generated SQL Created: 10/Oct/13  Updated: 01/Jul/14  Resolved: 08/May/14

Status: Closed
Project: i2b2 Core Software
Component/s: CRC Cell
Affects Version/s: 1.6.08
Fix Version/s: 1.7.02

Type: Bug Priority: Minor
Reporter: Nate Apathy Assignee: Mike Mendis
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Rank: 0|i000pr:
i2b2 Feature/s:
Constraints - Date, Queries
Participant/s:

 Description   
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


 Comments   
Comment by Janice Donahoe [ 08/May/14 ]
This issue has been fixed. We have verified that it is working correctly in 1.7.02 that will be released soon.
Generated at Fri Mar 29 09:27:09 UTC 2024 using Jira 8.20.11#820011-sha1:0629dd8d260e3954ece49053e565d01dabe11609.