[CORE-293] New SQL breakdowns returning error when run against SQL Server database Created: 31/Jan/18  Updated: 30/May/19  Resolved: 17/Apr/18

Status: Resolved
Project: i2b2 Core Software
Component/s: CRC Cell, Data
Affects Version/s: None
Fix Version/s: 1.7.10

Type: Bug Priority: Major
Reporter: Janice Donahoe Assignee: Janice Donahoe
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

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.
Participant/s:

 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.

 Comments   
Comment by Janice Donahoe [ 17/Apr/18 ]
Tested and verified it is is working as designed. This feature will be included in the 1.7.10 release.
Generated at Fri Mar 29 08:53:04 UTC 2024 using Jira 8.20.11#820011-sha1:0629dd8d260e3954ece49053e565d01dabe11609.