Details
Description
When setting the query_timing attribute to "SAME", an error is generated: invalid table name. The query that QueryToolUtil.ProcessControlFilei2b2 generates is below. I've included the XML request file as well. The fix involved commenting out a small set code. Once commented out, the query ran as expected. I've included a diff of my changes below as well.
----------
XML Request:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns6:request xmlns:ns2="http://www.i2b2.org/xsd/hive/pdo/1.1/" xmlns:ns3="http://www.i2b2.org/xsd/cell/crc/psm/1.1/" xmlns:ns4="http://www.i2b2.org/xsd/cell/crc/psm/analysisdefinition/1.1/" xmlns:ns5="http://www.i2b2.org/xsd/cell/crc/psm/querydefinition/1.1/" xmlns:ns6="http://www.i2b2.org/xsd/hive/msg/1.1/" xmlns:ns7="http://www.i2b2.org/xsd/hive/msg/result/1.1/">
<message_header>
<i2b2_version_compatible>1.3</i2b2_version_compatible>
<hl7_version_compatible>2.4</hl7_version_compatible>
<sending_application>
<application_name>i2b2web</application_name>
<application_version>1.3</application_version>
</sending_application>
<sending_facility>
<facility_name>i2b2Hive</facility_name>
</sending_facility>
<receiving_application>
<application_name>i2b2web</application_name>
<application_version>1.3</application_version>
</receiving_application>
<receiving_facility>
<facility_name>i2b2Hive</facility_name>
</receiving_facility>
<datetime_of_message>2010-01-08T13:08:02.694-05:00</datetime_of_message>
<security>
<domain>i2b2demo</domain>
<username>demo</username>
<password>demouser</password>
</security>
<message_type>
<message_code>Q04</message_code>
<event_type>EQQ</event_type>
</message_type>
<message_control_id>
<message_num>JSxtDRzlokfsSOzI6SiZK6xtWCPRLaNBBeur</message_num>
<instance_num>1</instance_num>
</message_control_id>
<processing_id>
<processing_id>P</processing_id>
<processing_mode>I</processing_mode>
</processing_id>
<accept_acknowledgement_type>AL</accept_acknowledgement_type>
<application_acknowledgement_type>AL</application_acknowledgement_type>
<country_code>US</country_code>
<project_id>Demo</project_id>
</message_header>
<request_header>
<result_waittime_ms>900000</result_waittime_ms>
<request_type>CRC</request_type>
</request_header>
<message_body>
<ns3:psmheader>
<user group="i2b2demo" login="demo">demo</user>
<patient_set_limit>0</patient_set_limit>
<estimated_time>0</estimated_time>
<request_type>CRC_QRY_runQueryInstance_fromQueryDefinition</request_type>
</ns3:psmheader>
<ns3:request xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns3:query_definition_requestType">
<query_definition>
<query_name>TEST</query_name>
<query_timing>SAME</query_timing>
<specificity_scale>0</specificity_scale>
<panel name="">
<panel_number>1</panel_number>
<panel_accuracy_scale>0</panel_accuracy_scale>
<invert>0</invert>
<item>
<hlevel>0</hlevel>
<item_name>Male</item_name>
<item_key>\\i2b2\i2b2\Demographics\Gender\Male\</item_key>
<dim_tablename>concept_dimension</dim_tablename>
<dim_columnname>concept_path</dim_columnname>
<dim_dimcode>\i2b2\Demographics\Gender\Male\</dim_dimcode>
<dim_columndatatype>T</dim_columndatatype>
<item_is_synonym>false</item_is_synonym>
</item>
</panel>
</query_definition>
<result_output_list>
<result_output priority_index="1" name="PATIENT_COUNT_XML"/>
<result_output priority_index="2" name="PATIENTSET"/>
</result_output_list>
</ns3:request>
</message_body>
</ns6:request>
----------
Query Generated:
SELECT encounter_num, patient_num, 1 panel_count
into i2b2demodata.QUERY_GLOBAL_TEMP
FROM (
INSERT INTO i2b2demodata.QUERY_GLOBAL_TEMP (patient_num, encounter_num, panel_count)
SELECT patient_num, encounter_num, 1
FROM (
SELECT encounter_num, patient_num
FROM i2b2demodata.observation_fact
WHERE (((concept_cd IN (
SELECT concept_cd
FROM i2b2demodata.concept_dimension c
WHERE concept_path LIKE '\i2b2\Demographics\Gender\Male\%')) ))
)t )t
<*>
----------
The Fix:
--- i2b2core-src_1-4-RC2_untouched/edu.harvard.i2b2.crc/src/server/edu/harvard/i2b2/crc/dao/setfinder/querybuilder/QueryToolUtil.java
+++ i2b2core-src_1-4-RC2/edu.harvard.i2b2.crc/src/server/edu/harvard/i2b2/crc/dao/setfinder/querybuilder/QueryToolUtil.java
@@ -1506,12 +1506,13 @@
+ TEMP_TABLE_PATIENT_ID + ", " + panelCount
+ " FROM ( " + "\r\n" + querySQL + ") t ";
- querySQL = "SELECT " + TEMP_TABLE_ENCOUNTER_ID
- + ", " + TEMP_TABLE_PATIENT_ID + ", "
- + panelCount + " panel_count into "
- + getDbSchemaName() + TEMP_TABLE
- + tableSuffix + " FROM ( " + "\r\n"
- + querySQL + ") t ";
+ // this line causes the query to be malformed
+ //querySQL = "SELECT " + TEMP_TABLE_ENCOUNTER_ID
+ // + ", " + TEMP_TABLE_PATIENT_ID + ", "
+ // + panelCount + " panel_count into "
+ // + getDbSchemaName() + TEMP_TABLE
+ // + tableSuffix + " FROM ( " + "\r\n"
+ // + querySQL + ") t ";
} else {
String occuranceSql = " ";
----------
XML Request:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns6:request xmlns:ns2="http://www.i2b2.org/xsd/hive/pdo/1.1/" xmlns:ns3="http://www.i2b2.org/xsd/cell/crc/psm/1.1/" xmlns:ns4="http://www.i2b2.org/xsd/cell/crc/psm/analysisdefinition/1.1/" xmlns:ns5="http://www.i2b2.org/xsd/cell/crc/psm/querydefinition/1.1/" xmlns:ns6="http://www.i2b2.org/xsd/hive/msg/1.1/" xmlns:ns7="http://www.i2b2.org/xsd/hive/msg/result/1.1/">
<message_header>
<i2b2_version_compatible>1.3</i2b2_version_compatible>
<hl7_version_compatible>2.4</hl7_version_compatible>
<sending_application>
<application_name>i2b2web</application_name>
<application_version>1.3</application_version>
</sending_application>
<sending_facility>
<facility_name>i2b2Hive</facility_name>
</sending_facility>
<receiving_application>
<application_name>i2b2web</application_name>
<application_version>1.3</application_version>
</receiving_application>
<receiving_facility>
<facility_name>i2b2Hive</facility_name>
</receiving_facility>
<datetime_of_message>2010-01-08T13:08:02.694-05:00</datetime_of_message>
<security>
<domain>i2b2demo</domain>
<username>demo</username>
<password>demouser</password>
</security>
<message_type>
<message_code>Q04</message_code>
<event_type>EQQ</event_type>
</message_type>
<message_control_id>
<message_num>JSxtDRzlokfsSOzI6SiZK6xtWCPRLaNBBeur</message_num>
<instance_num>1</instance_num>
</message_control_id>
<processing_id>
<processing_id>P</processing_id>
<processing_mode>I</processing_mode>
</processing_id>
<accept_acknowledgement_type>AL</accept_acknowledgement_type>
<application_acknowledgement_type>AL</application_acknowledgement_type>
<country_code>US</country_code>
<project_id>Demo</project_id>
</message_header>
<request_header>
<result_waittime_ms>900000</result_waittime_ms>
<request_type>CRC</request_type>
</request_header>
<message_body>
<ns3:psmheader>
<user group="i2b2demo" login="demo">demo</user>
<patient_set_limit>0</patient_set_limit>
<estimated_time>0</estimated_time>
<request_type>CRC_QRY_runQueryInstance_fromQueryDefinition</request_type>
</ns3:psmheader>
<ns3:request xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns3:query_definition_requestType">
<query_definition>
<query_name>TEST</query_name>
<query_timing>SAME</query_timing>
<specificity_scale>0</specificity_scale>
<panel name="">
<panel_number>1</panel_number>
<panel_accuracy_scale>0</panel_accuracy_scale>
<invert>0</invert>
<item>
<hlevel>0</hlevel>
<item_name>Male</item_name>
<item_key>\\i2b2\i2b2\Demographics\Gender\Male\</item_key>
<dim_tablename>concept_dimension</dim_tablename>
<dim_columnname>concept_path</dim_columnname>
<dim_dimcode>\i2b2\Demographics\Gender\Male\</dim_dimcode>
<dim_columndatatype>T</dim_columndatatype>
<item_is_synonym>false</item_is_synonym>
</item>
</panel>
</query_definition>
<result_output_list>
<result_output priority_index="1" name="PATIENT_COUNT_XML"/>
<result_output priority_index="2" name="PATIENTSET"/>
</result_output_list>
</ns3:request>
</message_body>
</ns6:request>
----------
Query Generated:
SELECT encounter_num, patient_num, 1 panel_count
into i2b2demodata.QUERY_GLOBAL_TEMP
FROM (
INSERT INTO i2b2demodata.QUERY_GLOBAL_TEMP (patient_num, encounter_num, panel_count)
SELECT patient_num, encounter_num, 1
FROM (
SELECT encounter_num, patient_num
FROM i2b2demodata.observation_fact
WHERE (((concept_cd IN (
SELECT concept_cd
FROM i2b2demodata.concept_dimension c
WHERE concept_path LIKE '\i2b2\Demographics\Gender\Male\%')) ))
)t )t
<*>
----------
The Fix:
--- i2b2core-src_1-4-RC2_untouched/edu.harvard.i2b2.crc/src/server/edu/harvard/i2b2/crc/dao/setfinder/querybuilder/QueryToolUtil.java
+++ i2b2core-src_1-4-RC2/edu.harvard.i2b2.crc/src/server/edu/harvard/i2b2/crc/dao/setfinder/querybuilder/QueryToolUtil.java
@@ -1506,12 +1506,13 @@
+ TEMP_TABLE_PATIENT_ID + ", " + panelCount
+ " FROM ( " + "\r\n" + querySQL + ") t ";
- querySQL = "SELECT " + TEMP_TABLE_ENCOUNTER_ID
- + ", " + TEMP_TABLE_PATIENT_ID + ", "
- + panelCount + " panel_count into "
- + getDbSchemaName() + TEMP_TABLE
- + tableSuffix + " FROM ( " + "\r\n"
- + querySQL + ") t ";
+ // this line causes the query to be malformed
+ //querySQL = "SELECT " + TEMP_TABLE_ENCOUNTER_ID
+ // + ", " + TEMP_TABLE_PATIENT_ID + ", "
+ // + panelCount + " panel_count into "
+ // + getDbSchemaName() + TEMP_TABLE
+ // + tableSuffix + " FROM ( " + "\r\n"
+ // + querySQL + ") t ";
} else {
String occuranceSql = " ";
Attachments
Issue Links
- clone of
-
CORE-16 query_timing of SAME causes error in sql query generation.
- Closed