SQL query using common_table_expression Failure in Codeigniter but not SQL Studio

Question

I created a query in MS SQL Management Studio for SQL Server 2012, which uses common_table_expression to join the table myself. It works correctly in MS SQL Studio, but the result or error is not returned in Codeigniter.

Request:

WITH rows AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY u.[PatientID], u.[CreateDate]) AS rn FROM ( SELECT a.[PatientID], a.[BGValue], a.[CreateDate], a.[HospitalUnit], 'MSHA' as 'Hospital', h.[system_name], f.[facility_code], f.[facility_name], 'IV' as 'Treatment' FROM [Analytics].[dbo].[MSHA_IVTreatment] a JOIN [Analytics].[dbo].[MSHA_Patients] p ON p.[PatientID] = a.[PatientID] JOIN [Analytics].[dbo].[Facilities] f ON f.[facility_code] = p.[facility_code] JOIN [Analytics].[dbo].[Hospitals] h ON f.[hospital] = h.[hospital] WHERE a.[CreateDate] BETWEEN '2013-05-01' AND '2013-05-31' UNION ALL SELECT a.[PatientID], a.[BGValue], a.[CreateDate], a.[HospitalUnit], 'MSHA' as 'Hospital', h.[system_name], f.[facility_code], f.[facility_name], 'SubQ' as 'Treatment' FROM [Analytics].[dbo].[MSHA_SubQTreatment] a JOIN [Analytics].[dbo].[MSHA_Patients] p ON p.[PatientID] = a.[PatientID] JOIN [Analytics].[dbo].[Facilities] f ON f.[facility_code] = p.[facility_code] JOIN [Analytics].[dbo].[Hospitals] h ON f.[hospital] = h.[hospital] WHERE a.[CreateDate] BETWEEN '2013-05-01' AND '2013-05-31' ) u ) SELECT mc.[PatientID], mc.[BGValue], mc.[CreateDate], mc.[Hospital], mc.[system_name] as 'System', mc.[facility_code], mc.[facility_name], mc.[HospitalUnit], DATEDIFF(second, mp.[CreateDate], mc.[CreateDate])/60 as 'Interval', mc.[Treatment] FROM rows mc JOIN rows mp ON mc.rn = mp.rn + 1 and mc.[PatientID] = mp.[PatientID] ORDER BY mc.[CreateDate] ASC; 

When I put this in a variable and try to get the result in codeigniter, nothing is returned:

 $result = $this->db->query($query); CI_DB_sqlsrv_result Object ( [conn_id] => Resource id #30 [result_id] => Resource id #41 [result_array] => Array ( ) [result_object] => Array ( ) [custom_result_object] => Array ( ) [current_row] => 0 [num_rows] => -1 [row_data] => ) 

I tried to put the request in a transaction, but got the same result.

Any insight is greatly appreciated.

Answer 1

Perhaps it will be useful for you -

 ;WITH [rows] AS ( SELECT a.[PatientID] , a.[BGValue] , a.[createdate] , a.[HospitalUnit] , Hospital = 'MSHA' , h.[system_name] , f.[facility_code] , f.[facility_name] , u.Treatment , rn = ROW_NUMBER() OVER (ORDER BY u.[PatientID], u.[createdate]) FROM ( SELECT PatientID , BGValue , createdate , HospitalUnit , Treatment = 'IV' FROM dbo.MSHA_IVTreatment WHERE createdate BETWEEN '20130501' AND '20130531' UNION ALL SELECT PatientID , BGValue , createdate , HospitalUnit , Treatment = 'SubQ' FROM dbo.MSHA_SubQTreatment WHERE createdate BETWEEN '20130501' AND '20130531' ) a JOIN [dbo].[MSHA_Patients] p ON p.[PatientID] = a.[PatientID] JOIN [dbo].[Facilities] f ON f.[facility_code] = p.[facility_code] JOIN [dbo].[Hospitals] h ON f.[Hospital] = h.[Hospital] ) SELECT mc.[PatientID] , mc.[BGValue] , mc.[createdate] , mc.[Hospital] , [System] = mc.[system_name] , mc.[facility_code] , mc.[facility_name] , mc.[HospitalUnit] , [Interval] = DATEDIFF(SECOND, mp.[createdate], mc.[createdate]) / 60 , mc.[Treatment] FROM [rows] mc JOIN [rows] mp ON mc.rn = mp.rn + 1 AND mc.[PatientID] = mp.[PatientID] ORDER BY mc.[createdate];