[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. |