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

Timeline filter not working if database is SQL Server

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.7.04
    • Fix Version/s: 1.7.06
    • Component/s: CRC Cell, Workbench
    • Labels:
      None
    • Rank:
      0|i001ov:
    • Affects View/s:
      Timeline View
    • i2b2 Sponsored Project/s:
      i2b2 Core
    • i2b2 Related Project/s:
      BBP
    • Affects Database/s:
      SQL Server
    • Reproduction Notes:
      Hide
      This issue can be reproduced in the 1.7 test environment.

      1. Log into the SQL project in the test environment
      2. Run a query using the concept of hypertension (make sure Timeline is checked)
      3. In the timeline if you look at demo patient 1000000013 you will notice there are the following 3 instances appearing for Unspecified essential hypertension

      First observation:
      Start date: 2001-01-02
      modifier_cd: 2
      instance_num: 1

      Second observation:
      Start date: 2007-09-07
      modifier_cd: 2
      instance_num: 1

      Third observation:
      Start date: 2007-09-10
      modifier_cd: 2
      instance_num: 1

      4. Click on Create model for Timeline
      5. Change the filter to First
      6. Click on Render a Timeline
      7. Notice the timeline still displays all the observations for patient 1000000013
      8. Look at the received xml and you will notice the above three instances are returned from the CRC.

      The only one that should be returned is the first observation of Unspecified essential hypertension

      First observation:
      Start date: 2001-01-02
      modifier_cd: 2
      instance_num: 1
      Show
      This issue can be reproduced in the 1.7 test environment. 1. Log into the SQL project in the test environment 2. Run a query using the concept of hypertension (make sure Timeline is checked) 3. In the timeline if you look at demo patient 1000000013 you will notice there are the following 3 instances appearing for Unspecified essential hypertension First observation: Start date: 2001-01-02 modifier_cd: 2 instance_num: 1 Second observation: Start date: 2007-09-07 modifier_cd: 2 instance_num: 1 Third observation: Start date: 2007-09-10 modifier_cd: 2 instance_num: 1 4. Click on Create model for Timeline 5. Change the filter to First 6. Click on Render a Timeline 7. Notice the timeline still displays all the observations for patient 1000000013 8. Look at the received xml and you will notice the above three instances are returned from the CRC. The only one that should be returned is the first observation of Unspecified essential hypertension First observation: Start date: 2001-01-02 modifier_cd: 2 instance_num: 1
    • Testing Notes:
      Hide
      TEST STATUS: Completed
      COMPLETION DATE: 07/22/2015

      ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

      Test Date: 07/22/2015
      Build Number:

      Clients Tested :
           i2b2 Workbench

      Environments Tested :
           Databases: SQL Server and Oracle
           Client OS: Windows and Macintosh

      Test Comments:
      Tested with the latest server build and it appears to be working correctly. I verified the appropriate observations are returned from the CRC based on the filter that is defined.

      In addition to testing First and Last I also verified that the Max and Min filters are still working.

      ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

      Test Date: 07/21/2015
      Build Number: 1.7.06.0001

      Clients Tested :
           i2b2 Workbench

      Environments Tested :
           Databases: SQL Server
           Client OS: Windows and Macintosh

      Test Comments:
      Tested with the latest workbench build (1.7.06.0001) and it is not working correctly.

      ISSUE FOUND:
      An error is returned from the server when you try to render a timeline in which the filter has been defined as one of the following:
      Max, Min, First, or Last

      In the XML message received from the server the cause of the error is due to incorrect syntax near the keyword 'with'.
      Show
      TEST STATUS: Completed COMPLETION DATE: 07/22/2015 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Test Date: 07/22/2015 Build Number: Clients Tested :      i2b2 Workbench Environments Tested :      Databases: SQL Server and Oracle      Client OS: Windows and Macintosh Test Comments: Tested with the latest server build and it appears to be working correctly. I verified the appropriate observations are returned from the CRC based on the filter that is defined. In addition to testing First and Last I also verified that the Max and Min filters are still working. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Test Date: 07/21/2015 Build Number: 1.7.06.0001 Clients Tested :      i2b2 Workbench Environments Tested :      Databases: SQL Server      Client OS: Windows and Macintosh Test Comments: Tested with the latest workbench build (1.7.06.0001) and it is not working correctly. ISSUE FOUND: An error is returned from the server when you try to render a timeline in which the filter has been defined as one of the following: Max, Min, First, or Last In the XML message received from the server the cause of the error is due to incorrect syntax near the keyword 'with'.

      Description

      In the timeline users can filter the observations by the first or last instance as well as the maximum or minimum instance of an observation.

      When the i2b2 database is SQL Server the filter is getting ignored and the PDO returned from the CRC contains all observations. The filter is working when the i2b2 database is an Oracle database.

      IMPORTANT NOTE: At this time I am not able to verify whether or not it is working correctly against a PostgreSQL database.

        Attachments

          Activity

            People

            • Assignee:
              jmd86 Janice Donahoe
              Reporter:
              jmd86 Janice Donahoe
              Participant/s:
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Git Source Code