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

New SQL breakdowns returning error when run against SQL Server database

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.7.10
    • Component/s: CRC Cell, Data
    • Labels:
      None
    • Rank:
      0|hzzzyg:
    • Affects View/s:
      Query Tool
    • i2b2 Feature/s:
      Analysis Breakdowns
    • i2b2 Sponsored Project/s:
      i2b2 Core
    • Affects Database/s:
      SQL Server
    • Developer Notes:
      Added the variable {{{DATABASE_NAME}}} so if that is detected the CRC will replace it with the actual database name found in the crc datasource.

      Description

      The ability to add a SQL query to the value column in the QT_BREAKDOWN_PATH table is a new feature to be included in the 1.7.10 release. The four sample SQL breakdowns that are to be delivered with the i2b2 demo data are not working when running an i2b2 query against a SQL Server database. If one or more are selected an error is returned instead of the correct value.

      This is happening because the SQL statement in the breakdown is not formatted correctly for SQL Server. The database name & database object schema is not preceding the table name.

      EXAMPLE (using i2b2 demo data database & schema): i2b2demodata.dbo.visit_dimension


      Current SQL statement (incorrect):

      select length_of_stay as patient_range, count(distinct a.PATIENT_num) as patient_count from visit_dimension a, #DX b where a.patient_num = b.patient_num group by a.length_of_stay order by 1

      In order for the above query to work it would need to be formatted as follows:

      select length_of_stay as patient_range, count(distinct a.PATIENT_num) as patient_count from i2b2demodata.dbo.visit_dimension a, #DX b where a.patient_num = b.patient_num group by a.length_of_stay order by 1


      NOTE: This issue is only happening when running the query against a SQL Server database. It works correctly for Oracle and PostgreSQL.

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Git Source Code