Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.3.00
-
None
-
Query Tool
-
Constraints - Value
Description
When choosing a concept with numeric results (e.g. loinc 2089-1) then if you choose >= or <= any value, you always get the total number of patients for that concept returned.
e.g. LOINC 2089-1 has 20187 patients
If I set value < 100 I get 6542 patients.
If I set value > 100 I get 14682 patients.
If I set value = 100 I get 295 patients.
If I set value <=100 I get 20187 patients which is incorrect.
If I set value >=100 I get 20187 patients which is incorrect.
Note: The workaround is to always use > or < so it's a minor bug. So instead of >=100 use >99.999 (or however many decimal points we can).
Here is the generated sql from the webclient (which is missing the filter by value) and from the workbench (which is correct):
webclient: loinc 2089-1 >=100 generated sql using webclient that gives the incorrect count
INSERT INTO dbo.#global_temp_table (patient_num, panel_count)
SELECT patient_num, 1 FROM ( SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num
FROM dbo.observation_fact WITH(NOLOCK) WHERE (((concept_cd IN (select concept_cd from dbo.concept_dimension c
WITH(NOLOCK) where concept_path like '\LOINC\LP31388-9\LP15705-4\LP15493-7\LP15491-1\LP41294-7\2089-1%')) ))
group by patient_num having count >=1) t <*>
INSERT INTO dbo.#dx (patient_num) SELECT * FROM (
SELECT DISTINCT t.patient_num FROM dbo.#global_temp_table t WHERE panel_count = 1 ) q
workbench: loinc 2089-1 >=100 generated sql using workbench that gives the correct count
INSERT INTO dbo.#global_temp_table (patient_num, panel_count)
SELECT patient_num, 1 FROM ( SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num
FROM dbo.observation_fact WITH(NOLOCK) WHERE (((concept_cd IN (select concept_cd from dbo.concept_dimension c
WITH(NOLOCK) where concept_path like '\LOINC\LP31388-9\LP15705-4\LP15493-7\LP15491-1\LP41294-7\2089-1%'))
AND valtype_cd = 'N' AND nval_num >= 100 AND tval_char IN ('G','E','GE')))
group by patient_num having count >=1) t <*>
INSERT INTO dbo.#dx (patient_num) SELECT * FROM (
SELECT DISTINCT t.patient_num FROM dbo.#global_temp_table t WHERE panel_count = 1 ) q
e.g. LOINC 2089-1 has 20187 patients
If I set value < 100 I get 6542 patients.
If I set value > 100 I get 14682 patients.
If I set value = 100 I get 295 patients.
If I set value <=100 I get 20187 patients which is incorrect.
If I set value >=100 I get 20187 patients which is incorrect.
Note: The workaround is to always use > or < so it's a minor bug. So instead of >=100 use >99.999 (or however many decimal points we can).
Here is the generated sql from the webclient (which is missing the filter by value) and from the workbench (which is correct):
webclient: loinc 2089-1 >=100 generated sql using webclient that gives the incorrect count
INSERT INTO dbo.#global_temp_table (patient_num, panel_count)
SELECT patient_num, 1 FROM ( SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num
FROM dbo.observation_fact WITH(NOLOCK) WHERE (((concept_cd IN (select concept_cd from dbo.concept_dimension c
WITH(NOLOCK) where concept_path like '\LOINC\LP31388-9\LP15705-4\LP15493-7\LP15491-1\LP41294-7\2089-1%')) ))
group by patient_num having count >=1) t <*>
INSERT INTO dbo.#dx (patient_num) SELECT * FROM (
SELECT DISTINCT t.patient_num FROM dbo.#global_temp_table t WHERE panel_count = 1 ) q
workbench: loinc 2089-1 >=100 generated sql using workbench that gives the correct count
INSERT INTO dbo.#global_temp_table (patient_num, panel_count)
SELECT patient_num, 1 FROM ( SELECT /*+ index(observation_fact fact_cnpt_pat_enct_idx) */patient_num
FROM dbo.observation_fact WITH(NOLOCK) WHERE (((concept_cd IN (select concept_cd from dbo.concept_dimension c
WITH(NOLOCK) where concept_path like '\LOINC\LP31388-9\LP15705-4\LP15493-7\LP15491-1\LP41294-7\2089-1%'))
AND valtype_cd = 'N' AND nval_num >= 100 AND tval_char IN ('G','E','GE')))
group by patient_num having count >=1) t <*>
INSERT INTO dbo.#dx (patient_num) SELECT * FROM (
SELECT DISTINCT t.patient_num FROM dbo.#global_temp_table t WHERE panel_count = 1 ) q