sql server - SQL Query Using WITH common_table_expression Fails in Codeigniter but Not SQL Studio -

i created query in ms sql management studio sql server 2012 uses common_table_expression self join table. works correctly in ms sql studio in codeigniter no result or error returned.

the query:

with rows      (     select *, row_number() on (order u.[patientid], u.[createdate]) rn     (         select a.[patientid], a.[bgvalue], a.[createdate], a.[hospitalunit], 'msha' 'hospital', h.[system_name], f.[facility_code], f.[facility_name], 'iv' 'treatment'         [analytics].[dbo].[msha_ivtreatment]          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]          a.[createdate] between '2013-05-01' , '2013-05-31'          union          select a.[patientid], a.[bgvalue], a.[createdate], a.[hospitalunit], 'msha' 'hospital', h.[system_name], f.[facility_code], f.[facility_name], 'subq' 'treatment'         [analytics].[dbo].[msha_subqtreatment]          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]          a.[createdate] between '2013-05-01' , '2013-05-31'         ) u     )  select  mc.[patientid], mc.[bgvalue], mc.[createdate], mc.[hospital], mc.[system_name] 'system', mc.[facility_code], mc.[facility_name], mc.[hospitalunit], datediff(second, mp.[createdate], mc.[createdate])/60 'interval', mc.[treatment]    rows mc join    rows mp on      mc.rn = mp.rn + 1 , mc.[patientid] = mp.[patientid] order mc.[createdate] asc; 

when put variable , attempt retrieve result in codeigniter nothing 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 have tried putting query in transaction got same result.

any insight appreciated.

possible helpful -

;with [rows] (      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() on (order u.[patientid], u.[createdate])       (           select                  patientid                , bgvalue                , createdate                , hospitalunit                , treatment = 'iv'            dbo.msha_ivtreatment           createdate between '20130501' , '20130531'            union             select                  patientid                , bgvalue                , createdate                , hospitalunit                , treatment = 'subq'            dbo.msha_subqtreatment           createdate between '20130501' , '20130531'      )      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] [rows] mc join [rows] mp on mc.rn = mp.rn + 1 , mc.[patientid] = mp.[patientid] order mc.[createdate];