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

query_timing of SAME causes error in sql query generation.

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.4.00
    • 1.6.00
    • CRC Cell
    • None
    • VMware Image Release 1.4-RC2 Final
      Host OS: Fedora 11 32bit
      Client: Simply Axis2 1.1 client
    • Rank:
      0|i001db:
    • Timing - Panel level, Timing - Query level

    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 = " ";
       

      Attachments

        Issue Links

          Activity

            People

              mem61 Mike Mendis
              jeremy.nix@cchmc.org Jeremy Nix
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: