New SQL breakdowns returning error when run against SQL Server database

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.